As I continue to work with Microsoft BizTalk Server it still amazed me when I can find something useful. As I was building a BizTalk solution that involves getting data from our SQL Server table, I found an SMO method that allow me to create both the XML and the XML Schema (XSD) so I can use them in my BizTalk solution.
Here’s the quick route using PowerShell and SQL Server SMO (SQL Server Management Objects) to create your XML Schema from a SQL table. Keep in mind, in order to use SQL Server SMO you must have SQL Server 2005 or greater.
1. Here’s a simple T-SQL script to query a table:
[sourcecode language=”powershell”]
## – Prepare you SQL Script in ‘PowerShell’ Here-String:
## (for more Info on ‘Here-String’use the command: get-help about_Quoting_Rules
$Sqlqry1 = @’
Use Northwind
SELECT TOP 3 [OrderID]
,[CustomerID]
,[EmployeeID]
,[OrderDate]
,[RequiredDate]
,[ShippedDate]
,[ShipVia]
,[Freight]
,[ShipName]
,[ShipAddress]
,[ShipCity]
,[ShipRegion]
,[ShipPostalCode]
,[ShipCountry]
FROM [Northwind].[dbo].[Orders]
‘@;
[/sourcecode]
2. Then, the next three SMO lines we get the information and store it in our variable. Make sure to define the variable as an array so you can get the right format:
[sourcecode language=”powershell”]
## – Load the SQL Server SMO Assembly:
[System.Reflection.Assembly]::loadwithPartialName(‘Microsoft.SQLServer.SMO’)
## – Connect to your SQL Server Instance and execute your SQL Query:
$MySQL = New-Object(‘Microsoft.SqlServer.Management.SMO.Server’) ‘BIZTALK2K10DEV’;
$tbldata = $MySQL.Databases[‘Northwind’].ExecuteWithResults($Sqlqry1);
[/sourcecode]
3. The following line just check the first 10 records in your PowerShell object:
[sourcecode language=”powershell”]
## – Display the first 10 lines of your data:
($tbldata.Tables) | Select -First 10 $_ | Format-Table -AutoSize;
[/sourcecode]
4. Finally, we can use the “.WriteXML()” and “.WriteXmlSchema()” methods to create our XML and XSD files:
[sourcecode language=”powershell”]
## – Create you XML file with your data:
($tbldata).WriteXml(‘C:\PowerShellScripts\tblServiceFacts.xml’);
## – To create the XML Schema (.xsd file) use the following method:
($tbldata).WriteXmlSchema(‘C:\PowerShellScripts\tblServiceFacts.xsd’);
[/sourcecode]
We are ready to incorporate the XSD file in your BizTalk solution(s).
There’s a few things to keep in mind. The above version will work with SQL Server 2008 but will behave different with SQL Server 2005 and PowerShell V2.0 it might giving you an “overload” error.
So, if you encounter the following error, then here’s a possible how-to fix it: (it work for me but further testing in other environment gave me a different results. It’s all just about testing and understanding your tools.
[sourcecode language=”powershell”]
## – Create you XML file with your data:
$tbldata.WriteXml([string] ‘C:\PowerShellScripts\tblOrders.xml’);
## – To create the XML Schema (.xsd file) use the following method:
$tbldata.WriteXmlSchema([string] ‘C:\PowerShellScripts\tblOrders.xsd’);
[/sourcecode]
Don’t forget to test your code to make sure you’re getting what you want. The main code will work in both PowerShell v2.0/v3.0, and SQL Server 2008 (or greater).
Awesome Technology! I will come back to this issue later and provide the PowerShell V2 solution.