Microsoft KB Archive/272184

= How To Retrieve XML Data with an XPath Query in an ASP Client =

Article ID: 272184

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 Q272184



SUMMARY
This article demonstrates how to retrieve an eXtensible Markup Language (XML) data stream from a SQL Server 2000 mapping schema by using an XPath query for an Active Server Pages (ASP) client.

The sample also allows you to test XPath queries against the mapping schema. Two methods are used to show the results of the ASP XPath query.

In the first method, clicking the XPath with Parameter button calls the ASP, which in turn filters the resulting XML document based on the XPath the user supplies. This method calls the ASP page on the server each time you press the XPath with Parameters button.

In the second method, clicking the Apply XSL button loads the XML results from the ASP page into an XML data island. An Extensible Stylesheet Language (XSL) transformation is performed upon the data island and then displayed. Note that the ASP is not called until you press the XPath with Parameter button, thereby calling the ASP.



MORE INFORMATION
Paste the following code into an ASP page, named xpath.asp. Note the hard-coded path to the directory of the server from which the ASP reads the mapping schema:

D:\VirtualRoot\Schema

Note Customize this path. Place the mapping schema (code follows) in the virtual directory that is set up for the schema files. You must change User ID= and Password= to the correct values before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.

<%@ Language=VBScript %>

<%

' Const DBGUID_DEFAULT As String = &quot;{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}&quot; ' Const DBGUID_SQL As String = &quot;{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}&quot; ' Const DBGUID_MSSQLXML As String = &quot;{5D531CB2-E6Ed-11D2-B252-00C04F681B71}&quot; ' Const DBGUID_XPATH As String = &quot;{ec2a4293-e898-11d2-b1b7-00c04f680c56}&quot; dim cn    dim cmd dim txtQryString ' get the query string from the user's request on the post. txtQryString = trim(cstr(Request.QueryString(&quot;XPathRequest&quot;) )) ' create the connection object. set cn = Server.CreateObject(&quot;adodb.connection&quot;) ' connect to the server. cn.Open &quot;PROVIDER=SQLOLEDB;Data Source=(local);Initial Catalog=Northwind;uid= ;pwd= &quot; '  create a new ADODB.Command Set cmd = Server.CreateObject(&quot;ADODB.Command&quot;) '  establish connection for the command object to the database. Set cmd.ActiveConnection = cn             '   set the command type to an XPath query. cmd.Dialect = &quot;{ec2a4293-e898-11d2-b1b7-00c04f680c56}&quot; '  set the base path for the template directory cmd.Properties(&quot;Base Path&quot;) = &quot;D:\VirtualRoot\schema&quot; '  point to the schema file cmd.Properties(&quot;Mapping Schema&quot;) = &quot;CustomerOrder.xdr&quot; '  set the output to stream back to the client cmd.Properties(&quot;Output Stream&quot;) =   Response '  specify the XPath command to retrieve if len(trim(txtQryString) ) > 0 then cmd.CommandText = trim(txtQryString) else cmd.CommandText = &quot;Customers&quot; end if       '   write out the root node Response.Write(&quot; &quot;) '  execute the command. cmd.Execute, , 1024     ' 1024 is adExecuteStream '  write out the closing root tag. Response.Write(&quot; &quot;) '  close connection. cn.Close '  clean up objects used. Set cn = nothing Set cmd = Nothing %>

For the mapping schema, create the CustomerOrder.xdr file and paste the following XML into the file:  

         

 <AttributeType name = &quot;CustomerID&quot; /> <AttributeType name = &quot;CompanyName&quot; /> <AttributeType name = &quot;ContactName&quot; /> <attribute type = &quot;CustomerID&quot;  sql:field=&quot;CustomerID&quot; /> <attribute type = &quot;CompanyName&quot; sql:field=&quot;CompanyName&quot; /> <attribute type = &quot;ContactName&quot; sql:field=&quot;ContactName&quot; /> <element type=&quot;CustOrder&quot; > <sql:relationship key-relation=&quot;Customers&quot; key=&quot;CustomerID&quot; foreign-key=&quot;CustomerID&quot; foreign-relation=&quot;Orders&quot; /> </ElementType>

</Schema>

Create the following HTML page to display XML data: <HTML> <HEAD> <TITLE>Dand's Sample Html page</TITLE>

<script language=&quot;VBSCRIPT&quot;>

function btnPlain_onClick xslPeople.innerText = source.documentelement.xml textarea1.innerText = xmlsource end function

function btnTable_onClick xslPeople.innerhtml = source.transformnode(styletable.xmldocument) end function

<xml id=&quot;xmlsource&quot; src=&quot;xpath.asp&quot;>

<xml id=&quot;xslsource&quot; src=&quot;xpath.xsl&quot;>

<SCRIPT ID=clientEventHandlersVBS LANGUAGE=vbscript>

</SCRIPT> </HEAD> <BODY> <INPUT id=&quot;applyxsl&quot; name=&quot;applyxsl&quot; type=&quot;button&quot; value=&quot;Apply XSL&quot;> <INPUT type=&quot;text&quot; id=&quot;XpathFilterString&quot; name=&quot;XpathFilterString&quot; style=&quot;width=40%&quot; > <INPUT id=&quot;applyfilter&quot; name=&quot;applyfilter&quot; type=&quot;button&quot; value=&quot;XPath with Parameter&quot;> <div id=&quot;htmlout&quot;> </BODY> </HTML> Create xpath.xsl file and paste the following code in the file: <?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?> <xsl:stylesheet version=&quot;1.0&quot; xmlns:xsl=&quot;http://www.w3.org/TR/WD-xsl&quot; > <xsl:template match =&quot;//&quot; > <body topmargin=&quot;3&quot; leftmargin=&quot;3&quot; marginheight=&quot;0&quot; marginwidth=&quot;0&quot; bgcolor=&quot;#ffffff&quot;> </xsl:template> <xsl:template match =&quot;root&quot;> <xsl:for-each select=&quot;Customers&quot;> <xsl:apply-templates select =&quot;CustOrder&quot; /> </xsl:for-each> </xsl:template> <xsl:template match=&quot;CustOrder&quot;> <td bgColor=&quot;#F0F0F0&quot;><xsl:value-of select=&quot;@CustomerID&quot; /> <td bgColor=&quot;#F0F0F0&quot;><xsl:value-of select=&quot;@OrderID&quot; /> <td bgColor=&quot;#F0F0F0&quot;><xsl:value-of select=&quot;@OrderDate&quot; /> </xsl:template> </xsl:stylesheet>

XPath filters to a specific point within your XML document. To test the query, you can enter the following XPath queries into the text box. The default is set to Customers (note case sensitivity).

Customers Customers[@CustomerID='ALFKI'] Customers[@CustomerID=&quot;ALFKI&quot;]/CustOrder [@OrderID=&quot;10643&quot; ]

<div class="references_section">