How To Retrieve Relational Data with OpenXML in an ASP Client (272271)

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

How To Retrieve Relational Data with OpenXML in an ASP Client (272271)

SUMMARY

MORE INFORMATION

REFERENCES