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

Using ADO with SQL Native Client (SQL Server 2005)

Using ADO with SQL Native Client (SQL Server 2005 Books Online, September 2007)

Enabling SQL Native Client from ADO

Dim con As New ADODB.Connection

con.ConnectionString = "Provider=SQLNCLI;" _
         & "Server=(local);" _
         & "Database=AdventureWorks;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;" _
         & "MARS Connection=True;"
con.Open

Retrieving XML Column Data

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sXMLResult As String

con.ConnectionString = "Provider=SQLNCLI;" _
         & "Server=(local);" _
         & "Database=AdventureWorks;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;"

con.Open

' Get the xml data as a recordset.
Set rst.ActiveConnection = con
rst.Source = "SELECT AdditionalContactInfo FROM Person.Contact " _
   & "WHERE AdditionalContactInfo IS NOT NULL"
rst.Open

' Display the data in the recordset.
While (Not rst.EOF)
   sXMLResult = rst.Fields("AdditionalContactInfo").Value
   Debug.Print (sXMLResult)
   rst.MoveNext
End While

con.Close
Set con = Nothing

Retrieving UDT Column Data

Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim strOldUDT As String
Dim strNewUDT As String
Dim aryTempUDT() As String
Dim strTempID As String
Dim i As Integer

con.ConnectionString = "Provider=SQLNCLI;" _
         & "Server=(local);" _
         & "Database=AdventureWorks;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;"

con.Open

' Get the UDT value.
Set cmd.ActiveConnection = con
cmd.CommandText = "SELECT ID, Pnt FROM dbo.Points.ToString()"
Set rst = cmd.Execute
strTempID = rst.Fields(0).Value
strOldUDT = rst.Fields(1).Value

' Do something with the UDT by adding i to each point.
arytempUDT = Split(strOldUDT, ",")
i = 3
strNewUDT = LTrim(Str(Int(aryTempUDT(0)) + i)) + "," + _
   LTrim(Str(Int(aryTempUDT(1)) + i))

' Insert the new value back into the database.
cmd.CommandText = "UPDATE dbo.Points SET Pnt = '" + strNewUDT + _
   "' WHERE ID = '" + strTempID + "'"
cmd.Execute

con.Close
Set con = Nothing

Enabling and Using MARS

Dim con As New ADODB.Connection

con.ConnectionString = "Provider=SQLNCLI;" _
         & "Server=(local);" _
         & "Database=AdventureWorks;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;" _
         & "MARS Connection=True;"
con.Open

Dim recordset1 As New ADODB.Recordset
Dim recordset2 As New ADODB.Recordset

Dim recordsaffected As Integer
Set recordset1 =  con.Execute("SELECT * FROM Table1", recordsaffected, adCmdText)
Set recordset2 =  con.Execute("SELECT * FROM Table2", recordsaffected, adCmdText)

con.Close
Set con = Nothing