Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 272184 - Last Review: November 22, 2005 - Revision: 4.6
How To Retrieve XML Data with an XPath Query in an ASP Client
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=<username> and Password=<strong 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 = "{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}"
' Const DBGUID_SQL As String = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
' Const DBGUID_MSSQLXML As String = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
' Const DBGUID_XPATH As String = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"
dim cn
dim cmd
dim txtQryString
' get the query string from the user's request on the post.
txtQryString = trim(cstr(Request.QueryString("XPathRequest") ))
' create the connection object.
set cn = Server.CreateObject("adodb.connection")
' connect to the server.
cn.Open "PROVIDER=SQLOLEDB;Data Source=(local);Initial Catalog=Northwind;uid=<username>;pwd=<strong password>"
' create a new ADODB.Command
Set cmd = Server.CreateObject("ADODB.Command")
' establish connection for the command object to the database.
Set cmd.ActiveConnection = cn
' set the command type to an XPath query.
cmd.Dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"
' set the base path for the template directory
cmd.Properties("Base Path") = "D:\VirtualRoot\schema"
' point to the schema file
cmd.Properties("Mapping Schema") = "CustomerOrder.xdr"
' set the output to stream back to the client
cmd.Properties("Output Stream") = Response
' specify the XPath command to retrieve
if len(trim(txtQryString) ) > 0 then
cmd.CommandText = trim(txtQryString)
else
cmd.CommandText = "Customers"
end if
' write out the root node
Response.Write("<root>")
' execute the command.
cmd.Execute , , 1024 ' 1024 is adExecuteStream
' write out the closing root tag.
Response.Write("</root>")
' 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:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="CustOrder" sql:relation="Orders">
<AttributeType name = "CustomerID" />
<AttributeType name = "OrderID" />
<AttributeType name = "OrderDate" />
<AttributeType name = "ShipCity" />
<attribute type = "CustomerID" sql:field="CustomerID" />
<attribute type = "OrderID" sql:field="OrderID" />
<attribute type = "OrderDate" sql:field="OrderDate" />
<attribute type = "ShipCity" sql:field="ShipCity" />
</ElementType>
<ElementType name="Customers" sql:relation="Customers">
<AttributeType name = "CustomerID" />
<AttributeType name = "CompanyName" />
<AttributeType name = "ContactName" />
<attribute type = "CustomerID" sql:field="CustomerID" />
<attribute type = "CompanyName" sql:field="CompanyName" />
<attribute type = "ContactName" sql:field="ContactName" />
<element type="CustOrder" >
<sql:relationship key-relation="Customers"
key="CustomerID"
foreign-key="CustomerID"
foreign-relation="Orders" />
</element>
</ElementType>
</Schema>
Create the following HTML page to display XML data:
<HTML>
<HEAD>
<TITLE>Dand's Sample Html page</TITLE>
<script language="VBSCRIPT">
function btnPlain_onClick()
xslPeople.innerText = source.documentelement.xml
textarea1.innerText = xmlsource
end function
function btnTable_onClick()
xslPeople.innerhtml = source.transformnode(styletable.xmldocument)
end function
</script>
<xml id="xmlsource" src="xpath.asp">
</xml>
<xml id="xslsource" src="xpath.xsl">
</xml>
<SCRIPT ID=clientEventHandlersVBS LANGUAGE=vbscript>
<!--
Sub applyxsl_onclick
dim xmldom
dim xsldom
set xmldom = CreateObject("msxml2.domdocument")
set xsldom = CreateObject("msxml2.domdocument")
xmldom.async =false
xsldom.async =false
xmldom.loadXML(xmlsource.xml)
xsldom.loadXML(xslsource.xml)
htmlout.innerHTML = xmldom.transformNode(xsldom)
End Sub
Sub applyfilter_onclick
dim oHttp
dim xmldom
dim txtResult
dim txtRequestString
set oHttp = CreateObject("MSXML2.XMLHTTP")
set xmldom = CreateObject("msxml2.domdocument")
txtRequestString = "xpath.asp?XPathRequest=" & XpathFilterString.value
oHttp.open "GET",txtRequestString , false
oHttp.send
xmldom.async =false
xmldom.loadXML( oHttp.responseText )
htmlout.innerText = xmldom.xml
End Sub
-->
</SCRIPT>
</HEAD>
<BODY>
<INPUT id="applyxsl" name="applyxsl" type="button" value="Apply XSL">
<INPUT type="text" id="XpathFilterString" name="XpathFilterString" style="width=40%" >
<INPUT id="applyfilter" name="applyfilter" type="button" value="XPath with Parameter">
<div id="htmlout"></div>
</BODY>
</HTML>
Create xpath.xsl file and paste the following code in the file:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/TR/WD-xsl" >
<xsl:template match ="http://support.microsoft.com/" >
<html>
<body topmargin="3" leftmargin="3" marginheight="0" marginwidth="0" bgcolor="#ffffff">
<table border="0" cellpadding="1" cellspacing="1" width="100%" style="color: black; font-family: arial; font-size: 12pt.;font-weight: 500" >
<thead>
<tr bgColor="#336699" align="center">
<th><STRONG><FONT color="white" size="2">Customer ID</FONT></STRONG></th>
<th><STRONG><FONT color="white" size="2">Order ID</FONT></STRONG></th>
<th><STRONG><FONT color="white" size="2">Order Date</FONT></STRONG></th>
</tr>
</thead>
<xsl:apply-templates select ="root" />
</table>
</body>
</html>
</xsl:template>
<xsl:template match ="root">
<xsl:for-each select="Customers">
<xsl:apply-templates select ="CustOrder" />
</xsl:for-each>
</xsl:template>
<xsl:template match="CustOrder">
<tr>
<td bgColor="#F0F0F0"><xsl:value-of select="@CustomerID" /></td>
<td bgColor="#F0F0F0"><xsl:value-of select="@OrderID" /></td>
<td bgColor="#F0F0F0"><xsl:value-of select="@OrderDate" /></td>
</tr>
</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="ALFKI"]/CustOrder [@OrderID="10643" ]
REFERENCES
For more information about SQL Server 2000, please see SQL Server Books Online.
For more information about XML, please see the following Web site:
Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.
APPLIES TO
- Microsoft Active Server Pages 4.0
- Microsoft SQL Server 2000 Standard Edition
- Microsoft XML Parser 2.6
| kbcodesnippet kbhowto KB272184 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please