Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 272271 - Last Review: July 1, 2004 - Revision: 4.5
How To Retrieve Relational Data with OpenXML in an ASP Client
This article was previously published under Q272271
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.
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=" Test OpenXML Sample ">
</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
For SQL Server 2000 and schemas, see SQL Server Books Online
For information on XML, see the following Microsoft Web site:
http://msdn.microsoft.com/xml
APPLIES TO
- Microsoft Active Server Pages 4.0
- Microsoft SQL Server 2000 Standard Edition
- Microsoft XML Parser 2.6
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