Using ADO to Execute SQLXML 4.0 Queries | SQL Server 2005

Using ADO to Execute SQLXML 4.0 Queries (SQL Server 2005 Books Online, September 2007)
In previous versions of SQLXML, HTTP-based query execution was supported using SQLXML IIS virtual directories and the SQLXML ISAPI filter. In SQLXML 4.0, these components have been removed as similar and overlapping functionality is now provided with native XML Web services in SQL Server 2005.

As an alternative, you can execute queries and use SQLXML 4.0 with your COM-based applications, by leveraging the SQLXML extensions to ActiveX Data Objects (ADO) that were first introduced in Microsoft Data Access Components (MDAC) 2.6 and later.

This topic demonstrates using SQLXML and ADO as part of a Visual Basic Scripting Edition (VBScript) application (a script with the .vbs file extension). It provides initial setup procedures to help you recreate and test query samples in the SQLXML 4.0 documentation.

WScript.Echo "Query process may take a few seconds to complete. Please be patient."

' Note that for SQL Server Native Client to be used as the data provider,
' it needs to be installed on the client computer first. Also, SQLXML extensions
' for ADO are used and available in MDAC 2.6 or later.

'Set script variables.
inputFile = "@@FILE_NAME@@"
strServer = "@@SERVER_NAME@@"
strDatabase = "@@DATABASE_NAME@@"
dbGuid = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"

' Establish ADO connection to SQL Server 2005 and
' create an instance of the ADO Command object.
Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
conn.Open "Provider=SQLXMLOLEDB.4.0;Data Provider=SQLNCLI;Server=" & strServer & _
          ";Database=" & strDatabase & ";Integrated Security=SSPI"
Set cmd.ActiveConnection = conn

' Create the input stream as an instance of the ADO Stream object.
Set inStream = CreateObject("ADODB.Stream")
inStream.Open
inStream.Charset = "utf-8"
inStream.LoadFromFile inputFile

' Set ADO Command instance to use input stream.
Set cmd.CommandStream = inStream

' Set the command dialect.
cmd.Dialect = dbGuid

' Set a second ADO Stream instance for use as a results stream.
Set outStream = CreateObject("ADODB.Stream")
outStream.Open

' Set dynamic properties used by the SQLXML ADO command instance.
cmd.Properties("XML Root").Value = "ROOT"
cmd.Properties("Output Encoding").Value = "UTF-8"

' Connect the results stream to the command instance and execute the command.
cmd.Properties("Output Stream").Value = outStream
cmd.Execute , , 1024

' Echo cropped/partial results to console.
WScript.Echo Left(outStream.ReadText, 1023)

inStream.Close
outStream.Close