Microsoft KB Archive/272271

= How To Retrieve Relational Data with OpenXML in an ASP Client =

Article ID: 272271

Article Last Modified on 7/1/2004

-

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 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.



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

Note You must change User ID= and 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 %>

<%   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 = &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; sXMLDoc = sXMLDoc & &quot;&quot; 'Setup the Connection

sConn = &quot;Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;User ID= ;Password= ;&quot; Set adoConn = server.CreateObject(&quot;ADODB.Connection&quot;) adoConn.CursorLocation = adUseClient adoConn.Open sConn sQuery = &quot;SP_OpenXML_Example&quot; ' Set the Command Set adoCmd = Server.CreateObject(&quot;ADODB.Command&quot;) 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 & &quot;: &quot; & adoRs.Fields(i).Value & &quot; &quot;) 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=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual Studio 6.0&quot;> <TITLE></TITLE> <SCRIPT ID=clientEventHandlersVBS LANGUAGE=vbscript>

</SCRIPT> </HEAD>

<FORM action=&quot;TestOpenXML.asp&quot; id=&quot;FORM2&quot; method=&quot;post&quot; name=&quot;form1&quot; target=&quot;_self&quot;> <INPUT id=&quot;TestOpenXML&quot; name=&quot;TestOpenXML&quot; type=&quot;submit&quot; value=&quot;&#13;&#10;Test OpenXML Sample&#13;&#10; &quot;> </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

<div class="references_section">