Binaryworld - Export SQL data to XML File (3 different ways) ... [ SQL Server -> XML ]

Many times we need to export sql server data to some form of XML. As we know that SQL Server can output data in XML format when you use "FOR XML" clause. You can use ADO and MSXML along with "FOR XML" sql query to generate XML files from SQL Server data. The following script is pure VB Script so if you want to use it in *.vbs file then also it will work without any problem.

You can accomplish this task with several different ways using the same code. I will explain 3 most common approaches.

1) Executing VB Script using SQL Server Job
2) Running DTS Package to execute the VB Script
3) Creating batch file (*.bat) to execute VB Script file (*.vbs) file.

Method-1 (Executing VB Script using SQL Server Job)

I find this is the most common approach when you have to export XML file on a regular interval. You can perform the following steps to implement this method to export sql data to XML file.

1. Open enterprise manager
2. Expand Server Node->Management->Jobs
3. Right click on the Job node and choose "New Job..."
4. Name your job (e.g. Nightly_XML_Export)
5. Go to Steps tab
6. Add new step, assign name (e.g. Top 3 orders export) to the newly created step.
7. Now in the Type Dropdown select "ActiveX Script" instead of "T-SQL"
8. Copy/Paste the following VB Script in the command Textbox

Click here to copy the following block

Dim oCmd, sSQL, oDom

''' If MSXML 4.0 is not installed this will not work!
Set oDom = CreateObject("Microsoft.XMLDOM")

Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection ="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(local)"

sSQL = "<?xml version=""1.0"" ?>"
sSQL = sSQL & "<NorthwindOrders xmlns=""http://www.northwind.com/schemas/orders"">"
sSQL = sSQL & "<sql:query xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">"
sSQL = sSQL & "select top 3 OrderId,CustomerId,OrderDate,ShipName from Northwind.dbo.Orders as SingleOrder order by orderid desc for xml auto,elements"
sSQL = sSQL & "</sql:query>"
sSQL = sSQL & "</NorthwindOrders>"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

oCmd.Properties("Output Encoding") = "utf-8"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024

oDom.Save "C:\testorders.xml"

Set oDom=Nothing
Set oCmd=Nothing

9. You can parse the script if you want or just click OK to close the step dialog box
10.Now before closing the Job dialog box make sure that Job owner account is set to high privilege account (e.g. sa or Administrator) so you can run VB Script without any problem.

10. Click ok to close and save the job
11. Run the job (Job should create a new file C:\testorders.xml)

Sample output file C:\testorders.xml

Click here to copy the following block

<?xml version="1.0" encoding="utf-8" ?>
<NorthwindOrders xmlns="http://www.northwind.com/schemas/orders">
<SingleOrder>
  <OrderId>11077</OrderId>
  <CustomerId>RATTC</CustomerId>
  <OrderDate>1998-05-06T00:00:00</OrderDate>
  <ShipName>Rattlesnake Canyon Grocery</ShipName>
</SingleOrder>
<SingleOrder>
  <OrderId>11076</OrderId>
  <CustomerId>BONAP</CustomerId>
  <OrderDate>1998-05-06T00:00:00</OrderDate>
  <ShipName>Bon app'</ShipName>
</SingleOrder>
<SingleOrder>
  <OrderId>11075</OrderId>
  <CustomerId>RICSU</CustomerId>
  <OrderDate>1998-05-06T00:00:00</OrderDate>
  <ShipName>Richter Supermarkt</ShipName>
</SingleOrder>
</NorthwindOrders>

Method-2 Running DTS Package to execute the VB Script

If you want to create DTS package for the same purpose then also you can use the same VB Script. Perform the following steps to create new DTS package for XML export.

1. Open enterprise manager
2. Expand Server Node->Data Transformation Services->Local Packages
3. Right click on the Local Packages node and choose "New Package"
4. Add new ActiveX Script Task
5. Copy/Paste the following VB Script in the script textbox

Click here to copy the following block

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim oCmd, sSQL, oDom

''' If MSXML 4.0 is not installed this will not work!
Set oDom = CreateObject("Microsoft.XMLDOM")

Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection ="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(local)"

sSQL = "<?xml version=""1.0"" ?>"
sSQL = sSQL & "<NorthwindOrders xmlns=""http://www.northwind.com/schemas/orders"">"
sSQL = sSQL & "<sql:query xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">"
sSQL = sSQL & "select top 3 OrderId,CustomerId,OrderDate,ShipName from Northwind.dbo.Orders as SingleOrder order by orderid desc for xml auto,elements"
sSQL = sSQL & "</sql:query>"
sSQL = sSQL & "</NorthwindOrders>"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

oCmd.Properties("Output Encoding") = "utf-8"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024

oDom.Save "C:\testorders.xml"

Set oDom=Nothing
Set oCmd=Nothing

Main = DTSTaskExecResult_Success

End Function

6. Save your package
7. Execute the package and you should see the new sample output file C:\testorders.xml

Method-3 Creating batch file (*.bat) to execute VB Script file (*.vbs) file

If you plan to run the export script as a *.bat file then you can create a new VBS file (e.g. c:\exportorders.vbs) using the same script I showed in the method-1. You can schedule your bat file using windows scheduler to execute at a certain schedule.