Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 272269 - Last Review: July 13, 2004 - Revision: 4.4
How To Retrieve XML Data by Using a SQL XML Query in an ASP Client
This article was previously published under Q272269
When you run an ADODB command stream and specify
SQL SELECT with the
FOR XML AUTO clause, an XML document stream is fetched from SQL Server, sent to the response object, and piped to the client.
Properties to be set for the properties of the ADODB.Command collection Output Stream and Dialect are as follows:
- Output Stream: Designates where the resulting XML data stream will be piped.
- Dialect: Defines the syntax and general rules that the provider uses to parse the string or stream. Setting the command language dialect specifies how the Microsoft OLE DB Provider for SQL Server interprets the command text that is received from ADO. The dialect is specified by a globally uniqe identifier (GUID) and is set using the Dialect property of the Command object.
Create a file called
TestXMLStream.ASP, paste in the following code, and make sure that there is script source access in TestXMLStream Internet Information Service (IIS) properties:
Note You must change uid=<user name> and pwd=<strong password> to the correct values before you run this code. Make sure that the uid has the appropriate permissions to perform this operation on the database.
<%@ Language=VBScript %>
<!--#include file="adovbs.inc" -->
<%
Dim adoCmd 'As ADODB.Command
Dim adoConn ' As ADODB.Connection
Dim adoStreamQuery ' As ADODB.Stream
Dim outStrm ' As ADODB.Stream
Dim txtResults ' String for results
dim sConn ' String for connection
dim CmdStream ' as ADODB.Stream
sConn = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;uid=<user name>;pwd=<strong password>"
Set adoConn = CreateObject("ADODB.Connection")
Set adoStreamQuery = CreateObject("ADODB.Stream")
adoConn.ConnectionString = sConn
adoConn.Open
Set adoCmd = CreateObject("ADODB.Command")
set adoCmd.ActiveConnection = adoConn
adoConn.CursorLocation = adUseClient
Set adoCmd.ActiveConnection = adoConn
sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
sQuery = sQuery & "<sql:query>SELECT * FROM PRODUCTS FOR XML AUTO</sql:query>"
sQuery = sQuery & "</ROOT>"
adoStreamQuery.Open ' Open the command stream so it may be written to
adoStreamQuery.WriteText sQuery, adWriteChar ' Set the input command stream's text with the query string
adoStreamQuery.Position = 0 ' Reset the position in the stream, otherwise it will be at EOS
Set adoCmd.CommandStream = adoStreamQuery ' Set the command object's command to the input stream set above
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" ' Set the dialect for the command stream to be a SQL query.
Set outStrm = CreateObject("ADODB.Stream") ' Create the output stream
outStrm.Open
adoCmd.Properties("Output Stream") = response ' Set command's output stream to the output stream just opened
adoCmd.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.
%>
For SQL Server 2000 and schema, see SQL Server Books Online
For information on XML, see the following Microsoft Web site:
APPLIES TO
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft Active Server Pages 4.0
- Microsoft SQL Server 2000 Standard Edition
- Microsoft Internet Information Services 5.0
- Microsoft XML Parser 2.6
- Microsoft XML Parser 3.0
- Microsoft XML Core Services 4.0
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