Using Streams for Command Input

ADO queries can be specified by setting the CommandText property on the Command object or by associating the stream with the Command object using the CommandStream property.This example demonstrates using a stream to access the Northwind database. It uses an Active Server Page (ASP) and is written in Microsoft Visual Basic® Scripting Edition.

Using XML Template Queries in Streams

The application initializes the ADO Stream object to contain query text:

Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open

The application requires a reference to the XML Namespace identified by the sql: prefix of the <sql:query> tag. The SELECT statement with a reference to the sql: Namespace takes this form:

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:query> SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML AUTO </sql:query>
</ROOT>

By using the FOR XML AUTO mode of the SELECT statement, this query requests that results are returned in XML format, rather than as a Recordset object. For more information, see Retrieving and Writing XML Data.

The command is then assigned to a string variable, and copied to the adoStreamQuery stream, which is associated with an ADO Command object:

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:query> SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML AUTO </sql:query>
</ROOT>"
adoStreamQuery.WriteText sQuery, adWriteChar
adoStreamQuery.Position = 0
Dim adoCmd 
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoCmd.CommandStream = adoStreamQuery

Setting the Command Language Dialect

The second requirement of the application is setting the command language dialect, which specifies how the Microsoft OLE DB Provider for SQL Server interprets the command text received from ADO. The dialect is specified by a globally unique identifier (GUID) and is set using the Dialect property of the Command object. The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) supports these values.

The command dialect for XML queries is specified as follows:

AdoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"