Microsoft KB Archive/272269

= How To Retrieve XML Data by Using a SQL XML Query in an ASP Client =

Article ID: 272269

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft Active Server Pages 4.0
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft Internet Information Services 5.0
 * Microsoft XML Parser 2.6
 * Microsoft XML Core Services 4.0
 * Microsoft XML Core Services 4.0

-



This article was previously published under Q272269



SUMMARY
When you run an ADODB command stream and specify SQL SELECT with the FOR XML AUTO clause, an XML document stream is fetched from SQL Server, sent to the response object, and piped to the client.

Properties to be set for the properties of the ADODB.Command collection Output Stream and Dialect are as follows:
 * Output Stream: Designates where the resulting XML data stream will be piped.
 * Dialect: Defines the syntax and general rules that the provider uses to parse the string or stream. Setting the command language dialect specifies how the Microsoft OLE DB Provider for SQL Server interprets the command text that is received from ADO. The dialect is specified by a globally uniqe identifier (GUID) and is set using the Dialect property of the Command object.



MORE INFORMATION
Create a file called TestXMLStream.ASP, paste in the following code, and make sure that there is script source access in TestXMLStream Internet Information Service (IIS) properties:

Note You must change uid= and pwd= to the correct values before you run this code. Make sure that the uid has the appropriate permissions to perform this operation on the database. <%@ Language=VBScript %>

<%

Dim adoCmd 'As ADODB.Command Dim adoConn       '   As ADODB.Connection Dim adoStreamQuery '  As ADODB.Stream Dim outStrm       '   As ADODB.Stream Dim txtResults    '   String for results dim sConn         '   String for connection dim CmdStream     '   as ADODB.Stream sConn = &quot;Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;uid= ;pwd= &quot; Set adoConn = CreateObject(&quot;ADODB.Connection&quot;) Set adoStreamQuery = CreateObject(&quot;ADODB.Stream&quot;) adoConn.ConnectionString = sConn adoConn.Open Set adoCmd = CreateObject(&quot;ADODB.Command&quot;) set adoCmd.ActiveConnection = adoConn adoConn.CursorLocation = adUseClient Set adoCmd.ActiveConnection = adoConn sQuery = &quot;&quot; sQuery = sQuery & &quot;SELECT * FROM PRODUCTS FOR XML AUTO&quot; sQuery = sQuery & &quot;&quot; adoStreamQuery.Open                            '   Open the command stream so it may be written to    adoStreamQuery.WriteText sQuery,  adWriteChar   '   Set the input command stream's text with the query string adoStreamQuery.Position = 0                    '   Reset the position in the stream, otherwise it will be at EOS Set adoCmd.CommandStream = adoStreamQuery      '   Set the command object's command to the input stream set above adoCmd.Dialect = &quot;{5D531CB2-E6Ed-11D2-B252-00C04F681B71}&quot;  ' Set the dialect for the command stream to be a SQL query. Set outStrm = CreateObject(&quot;ADODB.Stream&quot;)     '   Create the output stream outStrm.Open adoCmd.Properties(&quot;Output Stream&quot;) = response  '   Set command's output stream to the output stream just opened adoCmd.Execute, , adExecuteStream             '   Execute the command, thus filling up the output stream.

%>

