How To Retrieve XML Data by Using a SQL XML Query in an ASP Client (272269)

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=<user name> and pwd=<strong password> 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 %>

<!--#include file="adovbs.inc" -->
<%

    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 = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;uid=<user name>;pwd=<strong password>"
    Set adoConn = CreateObject("ADODB.Connection")    
    Set adoStreamQuery = CreateObject("ADODB.Stream")
    
    adoConn.ConnectionString = sConn
    adoConn.Open
    
    Set adoCmd = CreateObject("ADODB.Command")  
    set adoCmd.ActiveConnection = adoConn    
    
    adoConn.CursorLocation =  adUseClient
    
    Set adoCmd.ActiveConnection = adoConn
      sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
      sQuery = sQuery & "<sql:query>SELECT * FROM PRODUCTS FOR XML AUTO</sql:query>"
      sQuery = sQuery & "</ROOT>"
             
    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 = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"   ' Set the dialect for the command stream to be a SQL query.
    Set outStrm = CreateObject("ADODB.Stream")      '   Create the output stream
    outStrm.Open
    adoCmd.Properties("Output Stream") = response   '   Set command's output stream to the output stream just opened    
    adoCmd.Execute , , adExecuteStream              '   Execute the command, thus filling up the output stream.

%>

How To Retrieve XML Data by Using a SQL XML Query in an ASP Client (272269)

SUMMARY

MORE INFORMATION

REFERENCES