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

?>