Microsoft KB Archive/272266

= How To Retrieve XML Data by Using a Template File in an ASP Client =

Article ID: 272266

Article Last Modified on 11/22/2005

-

APPLIES TO


 * Microsoft Active Server Pages 4.0
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft XML Parser 2.6

-



This article was previously published under Q272266



SUMMARY
The following sample demonstrates how to run a SQL 2000 template file while specifying an XSL file to transform the XML data. More specifically, a query is issued against SQL Server 2000 by using the command stream of ADO 2.6: This query fetches the XML datastream.

This sample reads the template file products.xml from disk and sets the command text for the ADODB.Command for the query to run. Properties that are set for the Properties collection of the ADODB.Command include Base Path, Output Stream, Dialect, and XSL:
 * Base Path establishes the location where the template and the XSL files reside.
 * Output Stream designates where the resulting XML data stream will be piped.
 * The Dialect defines the syntax and the general rules that the provider uses to parse the string or the stream. When you set the command language dialect, this specifies how the Microsoft OLE DB provider for Microsoft SQL Server interprets the command text that is received from ADO. The dialect is specified by a globally unique identifier (GUID) and is set by using the Dialect property of the Command object.
 * You use the XSLT file name and the location to process the output XML stream.

NOTE: The Output Stream may be any object that supports an IStream or ISequentialStream interface. Objects that support the IStream interfaces are the ADODB.Stream, IIS5 Response object, and an MSXML DomDocument.

Finally, the sample also sets the XSL setting in order to transform the XML document.



MORE INFORMATION
Create a file called TestTemplate.asp, and then paste in the following code: <%@ Language=VBScript %>

<%   Dim cn                  '   As New ADODB.Connection Dim cmd                '   As New ADODB.Command Dim cmdStream          '   As New ADODB.Stream Dim cmdOutput          '   As New ADODB.Stream Dim txtOutputFileName  '   As String dim txtTemp            '   utility string set cn = CreateObject(&quot;ADODB.Connection&quot;) set cmd = CreateObject(&quot;ADODB.Command&quot;) set cmdStream = CreateObject(&quot;ADODB.Stream&quot;) set cmdOutput = CreateObject(&quot;ADODB.Stream&quot;) ' open the database connection cn.Open &quot;provider=sqloledb;data source=.;initial catalog=northwind;uid=sa;pwd=&quot; ' open the command stream, which contains the templated query cmdStream.Open ' set the character set to ASCII cmdStream.Charset = &quot;ascii&quot; ' set the command stream type to text, not binary cmdStream.Type = 1     'adTypeText ' read the template file from disk into the command stream to run txtTemp = server.MapPath(&quot;.&quot;) & &quot;\products.xml&quot; cmdStream.LoadFromFile txtTemp     ' App.Path & &quot;\products.xml&quot; ' set the command connection Set cmd.ActiveConnection = cn   ' set the command's command stream, thereby hooking the template query ' to the command you want to execute. Set cmd.CommandStream = cmdStream ' set the command dialect cmd.Dialect = &quot;{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}&quot; 'DBGUID_DEFAULT ' open the output stream to receive the results for the command execute. cmdOutput.Open ' set the base path for where the template file resides. Currently this must ' point to a file on disk, remote templates via http://...template.xml&quot; are not allowed. cmd.Properties(&quot;Base Path&quot;) = server.MapPath(&quot;.&quot;) ' App.Path ' set up the output stream that will receive the output of the command execute cmd.Properties(&quot;Output Stream&quot;) = Response ' set the XSL to process the output XML stream cmd.Properties(&quot;XSL&quot;) = &quot;products.xsl&quot; ' execute the command stream cmd.Execute, , adExecuteStream Set cmdOutput = Nothing Set cmdStream = Nothing Set cmd = Nothing Set cn = Nothing %>

  The previous example uses both a template and an XSL file.

Create and name the following template files products.xml:    %   SELECT     * FROM       Products WHERE      ProductName like '%' + @ProdName + '%' ORDER BY   ProductName FOR XML AUTO  Create and name the following XSL file products.xsl:    MSDN ADO Product Sample with SQL Server 2000 Features <table border=&quot;0&quot; cellPadding=&quot;1&quot; cellSpacing=&quot;1&quot; width=&quot;100%&quot; style=&quot;COLOR: black; FONT-FAMILY: Arial; FONT-SIZE: 12pt.;FONT-WEIGHT: 500&quot;> <tr bgColor=&quot;#336699&quot; align=&quot;center&quot;> <TD><P ><STRONG><FONT color=&quot;white&quot; size=&quot;2&quot;>Product ID:</FONT></STRONG></P></TD> <TD><P ><STRONG><FONT color=&quot;white&quot; size=&quot;2&quot;>Product Name:</FONT></STRONG></P></TD> <TD><P ><STRONG><FONT color=&quot;white&quot; size=&quot;2&quot;>Unit Price:</FONT></STRONG></P></TD> <TD><P ><STRONG><FONT color=&quot;white&quot; size=&quot;2&quot;>Units In Stock:</FONT></STRONG></P></TD> <TD><P ><STRONG><FONT color=&quot;white&quot; size=&quot;2&quot;>Restock Level:</FONT></STRONG></P></TD> <TD><P ><FONT color=&quot;white&quot; size=&quot;2&quot;><STRONG>Units On Order:</STRONG></FONT></P></TD> <xsl:for-each select=&quot;root/Products&quot;> <tr style=&quot;COLOR: black; FONT-FAMILY: Arial; FONT-SIZE: 0.8em; FONT-WEIGHT: 500&quot;> <td bgColor=&quot;#F0F0F0&quot;><xsl:value-of select=&quot;@ProductID&quot;/> <td bgColor=&quot;#F0F0F0&quot;><xsl:value-of select=&quot;@ProductName&quot;/> <td bgColor=&quot;#F0F0F0&quot;><xsl:value-of select=&quot;@UnitPrice&quot;/> <td bgColor=&quot;#F0F0F0&quot;><xsl:value-of select=&quot;@UnitsInStock&quot;/> <td bgColor=&quot;#F0F0F0&quot;><xsl:value-of select=&quot;@ReorderLevel&quot;/> <td bgColor=&quot;#F0F0F0&quot;><xsl:value-of select=&quot;@UnitsOnOrder&quot;/> </xsl:for-each> </xsl:template> </xsl:stylesheet>

<div class="references_section">