Article ID: 272271 - Last Review: July 1, 2004 - Revision: 4.5

How To Retrieve Relational Data with OpenXML in an ASP Client

View products that this article applies to.

This article was previously published under Q272271

SUMMARY

Running an OpenXML statement in SQL Server 2000 returns an XML data stream back to the client. This article contains a sample that prints the contents of the XML document back to the client application through an ADODB.Recordset.

Back to the top

MORE INFORMATION

Create a file called TestOpenXML.asp, and then paste in the following code:

Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Makesure that the User ID has the appropriate permissions to perform this operation on the database.

<%@ Language=VBScript %>

<!--#include file="adovbs.inc" -->
<%
    Dim adoConn     '   As ADODB.Connection
    Dim adoCmd      '   As ADODB.Command
    Dim adoRs       '   As ADODB.Recordset
    Dim sConn       '   As String
    Dim sQuery      '   As String    
    Dim sXMLDoc     '   As String
    
    ' Setup the Document   
    sXMLDoc = "<ROOT>"
    sXMLDoc = sXMLDoc & "<Customers CustomerID='VINET' ContactName='Paul Henriot'>"
    sXMLDoc = sXMLDoc & "<Orders CustomerID='VINET' EmployeeID='5' OrderDate='1996-07-04T00:00:00'>"
    sXMLDoc = sXMLDoc & "<Order_0020_Details OrderID='10248' ProductID='11' Quantity='12'/>"
    sXMLDoc = sXMLDoc & "<Order_0020_Details OrderID='10248' ProductID='42' Quantity='10'/>"
    sXMLDoc = sXMLDoc & "</Orders>"
    sXMLDoc = sXMLDoc & "</Customers>"
    sXMLDoc = sXMLDoc & "<Customers CustomerID='LILAS' ContactName='Carlos Gonzlez'>"
    sXMLDoc = sXMLDoc & "<Orders CustomerID='LILAS' EmployeeID='3' OrderDate='1996-08-16T00:00:00'>"
    sXMLDoc = sXMLDoc & "<Order_0020_Details OrderID='10283' ProductID='72' Quantity='3'/>"
    sXMLDoc = sXMLDoc & "</Orders>"
    sXMLDoc = sXMLDoc & "</Customers>"
    sXMLDoc = sXMLDoc & "</ROOT>"
    
    'Setup the Connection

    sConn = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;User ID=<username>;Password=<strong password>;"
    Set adoConn = server.CreateObject("ADODB.Connection")
    adoConn.CursorLocation = adUseClient
    adoConn.Open  sConn
       
    sQuery = "SP_OpenXML_Example"
    ' Set the Command
    Set adoCmd = Server.CreateObject("ADODB.Command")
    Set adoCmd.ActiveConnection = adoConn
    adoCmd.CommandText = sQuery
    ' Command Type is a Stored Procedure
    adoCmd.CommandType = adCmdStoredProc
    adoCmd.Parameters.Refresh
    adoCmd.Parameters.Item(1).Value = sXMLDoc
    Set adoRs = adoCmd.Execute()
    
    While Not adoRs.EOF
        For i = 0 To adoRs.Fields.Count - 1
            Response.write ( adoRs.Fields(i).Name & ":  " & adoRs.Fields(i).Value & "<br/>")
        Next 
        adoRs.MoveNext
    Wend
    
    adoRs.Close
    adoConn.Close
    set adoRs = Nothing
    set adoconn = nothing
    
%>

Use the following file to test the preceding ASP page. Create a file named TestOpenXml.htm, and then paste in the following html:

<HTML>
<HEAD>
<META name=VI60_defaultClientScript content=VBScript>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<SCRIPT ID=clientEventHandlersVBS LANGUAGE=vbscript>

</SCRIPT>
</HEAD>

<FORM action="TestOpenXML.asp" id="FORM2" method="post" name="form1" target="_self">
    <INPUT id="TestOpenXML" name="TestOpenXML" type="submit" value="&#13;&#10;Test OpenXML Sample&#13;&#10;  ">
</FORM>

</BODY>
</HTML>

Create the following stored procedure in the SQL Server 2000 Northwind database:

USE NORTHWIND
GO

CREATE PROCEDURE sp_OpenXML_Example
    @XMLDoc ntext
AS
    DECLARE @ReturnCode INT
    DECLARE @iDoc int

    EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @XMLDoc

    SELECT * FROM OpenXML(@iDoc, '/ROOT/Customers',1)
            WITH (CustomerID varchar(10), ContactName varchar(20))

    EXECUTE sp_xml_removedocument @iDoc

    SELECT @ReturnCode = 1
    RETURN @ReturnCode
GO

Back to the top