Getting XML from a SQL Server via VBScript

19 Nov 2004

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 "<" & Ucase(TableName) & "Table>"

ts.Write txtResults

ts.Write "

ts.Close

'Cleanup File write objects

'*******************************************************

set fso = Nothing

set ts = Nothing