How To Retrieve XML Data with an XPath Query in an ASP Client (272184)

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: 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 ="//" >
            <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" ]