Getting XML from a SQL Server via VBScript
I had reason to help a client the other day automate the transmission of data from a table they controlled in their system to my company on a daily basis in order to allow us to use that data in performing certain operations for them. Initially they were going to send a tab delimited file, and then I suggested they send XML. They indicated that they weren't comfortable formatting XML and I said ... “Have SQL Server handle that for you.” What resulted was an education for them on how to retrieve XML from SQL Server and the following code snippet:'*******************************************************'Example code for XML Export of table data'*******************************************************' By Tim Rayburn, Lead Architect, www.ghnonline.com' Date : 09/23/2004'*******************************************************'This is a modified version of an example file located on msdn.microsoft.com to suit'the specific purpose of this example. The original also used XML for the query, which'is not done in this file.'*******************************************************'Absolutely no warrantly expressed or implied.'This code is herby placed in the public domain.'******************************************************* 'Declare all variables '******************************************************* Dim adoCmd ' As ADODB.Command Dim adoConn ' As ADODB.Connection Dim outStrm ' As ADODB.Stream Dim txtResults ' String for results Dim sConn ' String for connection Dim sQuery ' Query String Dim fso 'File System Object Dim ts 'Text Stream Dim dbName 'The DNS name or IP address of your DB Server Dim UID 'The User Name to login to your database Dim PWD 'The Password to login to your database Dim Catalog 'The specific database to connect to Dim TableName 'The table we will retrieve data from 'Setup Variable Values (CHANGE THIS TO HAVE THE INFORMATION FOR YOUR DATABASE) '******************************************************* dbName = "DBSERVER" UID = "UID" PWD = "PWD" Catalog = "Northwind" TableName = "Employees" 'Connect to the database '******************************************************* sConn = "Provider=SQLOLEDB;Data Source=" & dbName sConn = sConn & ";Initial Catalog=" & Catalog & ";uid=" & UID & ";pwd=" & PWD Set adoConn = CreateObject("ADODB.Connection") adoConn.ConnectionString = sConn adoConn.CursorLocation = 3 adoConn.Open 'Create the Command Object & Query '******************************************************* Set adoCmd = CreateObject("ADODB.Command") set adoCmd.ActiveConnection = adoConn sQuery = "SELECT * FROM " & UCase(TableName) & " FOR XML AUTO, ELEMENTS" adoCmd.CommandText = sQuery 'Open the output stream and query the database '******************************************************* Set outStrm = CreateObject("ADODB.Stream") outStrm.Open adoCmd.Properties("Output Stream") = outStrm adoCmd.Execute , , 1024 'Set to the start of the stream, and then read all data. '******************************************************* outStrm.Position = 0 txtResults = outStrm.ReadText(-1) 'Cleanup Database objects '******************************************************* adoConn.Close set adoConn = Nothing set adoCmd = Nothing set outStrm = Nothing 'Output to a file '******************************************************* set fso = CreateObject("Scripting.FileSystemObject") set ts = fso.CreateTextFile("OutputFile.xml") ts.Write ""ts.Write txtResults ts.Write " ts.Close 'Cleanup File write objects '******************************************************* set fso = Nothing set ts = Nothing?>