How can I retrieve the results of a FOR XML query using ASP?

Friday, November 20, 2009

How can I retrieve the results of a FOR XML query using ASP? (51603 Requests)

Here is some sample code that you can use to write the results of a for xml query to an ASP page.


<%

Dim oCmd, sSQL

sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
       "select * from table for xml auto</sql:query></root>"

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "<your connection string>"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

''' This assumes you're using IIS 5
''' In IIS4 the Response object doesn't implement the IStream
''' interface.
oCmd.Properties("Output Stream") = Response
oCmd.Execute , , 1024

Set oCmd = Nothing

%>

If you're using IIS 4 or you want to load the results into a DOMDocument instead of writing them to the page you can use the example below instead.


<%

Dim oCmd, sSQL, oDom

Set oDom = Server.CreateObject("Msxml2.DOMDocument")

sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
       "select * from table for xml auto</sql:query></root>"

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "<your connection string>"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024

Set oCmd = Nothing

''' here you can write out the results if needed
Response.Write oDom.xml

%>

Other Resources:
HOWTO: Retrieve XML Data by Using a SQL XML Query in an ASP Client

HOWTO: Retrieve XML Data with an XPath Query in an ASP Client

Feedback

# OK but then?? 10/24/2001 4:01 AM Tryggve

Hi, What to do then? I only recive a lot of unformated data on my asp page. And I can´t see the information unless i take view source. I can see the xml formated code but it´s not visible. What more shall i do to have a xml file?

# OK but then?? 10/25/2001 6:28 PM Bryant

You may need to set the Response.ContentType = "text/xml" if you want the XML to be displayed in the browser. If you do not set this the browser will try to render your XML as HTML.

# Problems 10/30/2001 11:35 AM Harve

I'm trying to run your example but I get the following error on the line "oCmd.Properties("Output Stream") = Response" ADODB.Command (0x800A0CC1) Item cannot be found in the collection corresponding to the requested name or ordinal. I'm using IE 5.5, ADO 2.6, and IIS/5.0 Any help would be much appreciated!!

# Problems 10/30/2001 5:17 PM Bryant

This could be caused by a number of issues. Take a look at this FAQ for more information: http://sqlxml.org/sqlxml/faqs.xml?faq=25.

# Problems 10/30/2001 8:45 PM Harve

Thanks, I checked it out. I'm running MDAC2.6 and the latest adovbs.inc file (I'm pretty sure, anyway - the example uses 1024 as the actual value for the enum...). Anywhere else I should look? Thanks again...

# Problems 10/31/2001 12:02 AM Bryant

Make sure you are using the SQLOLEDB provider. If you are using this provider and you have MDAC 2.6 you shouldn't have any problems. You can email me the code your using (minus the password in the connection string) if it still doesn't work bryant@sqlxml.org.

# FORXML 12/1/2001 12:06 AM Menon

Why do I get this error : I am using FORXML on a win2000/SQL2000 machine testing using visual interdev 6.0 Whitespace is not allowed at this location. Line 26, Position 46 document.all("log").innerHTML = OutputXML & "

" & child.getAttribute("ProductName") & "

" ---------------------------------------------^ Thanks Menon

# FORXML 12/3/2001 2:54 PM Bryant

Can you either post your question to the sqlserver.xml newsgroup or email me with your question? These comments don't work that great for debugging things.

# platform discrepencies 12/13/2001 1:19 PM wrighte

I am running a dual box web system. On my DB box I am running win2k with SQL2000. On my web server I am running linux with apache. My question is, can I run a FOR XML style query on my win box and retrieve the data in server side code on my linux box since I don't have IIS to interface? I am using cold fusion as a my server side language but I am pretty flexible as far as implementation goes. if you know what I am asking please right to me as wrighte@oit.edu thanks.

# platform discrepencies 12/14/2001 1:09 PM Bryant

You could do this by setting up IIS on your SQL box (perhaps on an internal IP) and then load the XML via HTTP.

# Outputing the stream to a variable 12/21/2001 1:15 PM Buddy

Can I output the stream to a local variable? EX: DIM localVar ' as string . . . oCmd.Properties("Output Stream") = localVar oCmd.Execute , , 1024

# Problems 12/27/2001 4:12 PM Branka

Try this to be sure of ADO version: Response.write "
ADO Version = " & cn.Version & "
" Branka

# Outputing the stream to a variable 12/30/2001 4:37 PM Bryant

Sure. Just use an ADODB.Stream object and open it before assigning it.

# OK but then?? 2/18/2002 12:56 PM henry

I have the same problems as Trygve: xml does not appear in IE browser. Try Response.ContentType = "text/xml" . . . and it did not show-up . . . look at source, yes. Response.ContentType = "text/plain" and see the xml string in plain text. And ideas? Windows 200 sp2 /SQL 2000 sp2 / SQLXML3 / IIS 5/ADO 2.7

# OK but then?? 2/28/2002 12:52 PM HiKsFiles

I'm facing the exact same situation with the exact same setup as that of Henry. I don't know if anyone has found a solution to this problem, but I sure would appreciate if you have one. Help!!!

# A little different scenario 3/1/2002 3:49 PM Joy

ok - I have a query template stored on a SQLXML IIS virtual directory - I want to execute the query using ASP and send it to another virtual directory - is it possible? And if it is - how do you do it? Thanks - Joy

# A little different scenario 3/1/2002 3:49 PM Joy

# Speed 3/11/2002 9:29 PM Steve

I am trying to pull down 1 million rows (yes, I have to), and sending XML Explicit output directly to an ADODB.Stream gives horrible performance. One million rows also breaks the SQLXML ISAPI filter, so I can't use that. What else can I do to get the one million rows down in one fell swoop? This is for a a B2B type of transaction and all of it needs to get in, otherwise we'll have to go back to flat ASCII (yikes).

# Problems 3/15/2002 1:41 AM Ian Kirkland

When I used your URL above http://sqlxml.org/sqlxml/fags.xml?faq=25 I get the following error message: error 400.100 Bad request HResult: 0x80004005 Source: Microsoft SQL isapi extension Description: The virtual name could not be recognized

# Problems 3/15/2002 5:32 AM Ian Kirkland

I have managed to get asp files working with IIS 5 and now I get a strange message when I run your asp code above... ADODB.Command error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal. /xmltest/FORXML.asp, line 16 I know for a fact that the table that I am requesting does exist and I have not spelled it incorrectly any ideas????

# Problems 3/25/2002 11:49 AM Joy

I got the error "oCmd.Properties("Output Stream") = Response" ADODB.Command (0x800A0CC1) Item cannot be found in the collection corresponding to the requested name or ordinal." when I changed the connection string to try to connect to a remote server. Why is that? And if there is a virtual directory for the database I want to work with do I even need a connection string?

# Outputing the stream to a variable 4/23/2002 2:07 PM Alice

I got an error when I use the following statement: oCmd.Properties("Output Stream") = response Below are the error messages: Error Type: ADODB.Command (0x800A0CC1) Item cannot be found in the collection corresponding to the requested name or ordinal. What's wrong with my code?

# OK but then?? 5/13/2002 5:06 PM Robert Jeppesen

This happens to me too. For me it's because SQL server returns the xml marked as UTF-8 encoding while the content is ISO-8859-1. That makes Swedish characters () invalid and thus the xml parser freaks out. I don't know how to fix it.

# OK but then?? 5/13/2002 5:06 PM Robert Jeppesen

# OK but then?? 5/13/2002 5:07 PM Robert

# Outputing the stream to a variable 6/27/2002 4:51 PM Dave

You need to have "rovider=SQLOLEDB;" as part of your connection string

# Problems 6/28/2002 6:09 AM Dave

You need to have "Provider=SQLOLEDB;" as part of your connection string (Sorry I spelled it wrong up above)

# Retrieving XML in UTF-8 encoding 8/7/2002 3:35 PM Johnny B

When I use the above described method to retrieve the data as XML and write this to a new XML document, the XML data does not seem to be UTF-8 encoded. It contains characters which are not displayed by a tool like XML Spy. Also when importing the XML document with ASP code I get the next error: An invalid character was found in text content. I think this is due the encoding, which is not UTF-8 in my opinion. Who can help me here? Is there a way to manipulate the above code, to retrieve real UTF-8 encoded data? I alreade tried to put: in the XML document befor importing, but it did not help.

# Speed 8/29/2002 9:58 PM Marshall

I have done a system that works with 1 million records. It is slow though. The XML Stream I create makes the downloaded records huge. 50,000 records take about 8 minutes to download and process. You cannot do ASP pages with 1 million records. There is a 2GB limit on SOAP. You need to look into Dime attachments. Marrshall MFHOWLAND@ATTBI.COM

# Article 9/8/2002 11:52 PM Jeff G

There is a nice article on exporting SQL Server Data at http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp Check it out!!!

# Retrieving XML in UTF-8 encoding 9/24/2002 7:33 AM J

I have the same problem. Anyone have a solution??????

# Problems 10/9/2002 9:01 AM Shahab

I had the same problem. I managed to solve it by assigning a Connection Object to oCmd.ActiveConnection instead of a string. Here is my code: Dim oCmd, sSQL, objCon sSQL = "" & _ "" & _ "" & _ "SELECT " & _ "UserID AS 'ID'," & _ "'ID' AS 'ColumnName'," & _ "GroupID AS 'Column'," & _ "'GroupID' AS 'ColumnName'," & _ "Username AS 'Column'," & _ "'Username' AS 'ColumnName'," & _ "Password AS 'Column'," & _ "'Password' AS 'ColumnName'," & _ "Description AS 'Column'," & _ "'Description' AS 'ColumnName'" & _ "FROM tblUsers AS Row " & _ "FOR XML AUTO, ELEMENTS" & _ "" & _ "" 'Establish Database Connection Set objCon = Server.CreateObject("ADODB.Connection") With objCon .Open _ "provider=SQLXMLOLEDB.2.0;data provider=SQLOLEDB;" & _ "data source=(local);initial catalog=sqlTemplate", "sa", "!TSIsql1999*" End With Set oCmd = Server.CreateObject("ADODB.Command") With oCmd .ActiveConnection = objCon .CommandText = sSQL .Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" .Properties("Output Stream") = Response .Properties("xsl").Value = Server.MapPath("../Data_Manipulation/MSSQL/SQLXML/Users.xsl") .Properties("Output Encoding") = "utf-8" .Execute "", "", 1024 '1024 = adExecuteStream End With Set oCmd = Nothing I hope it helps.

# JScript 11/11/2002 9:33 AM Ciaran Ryan

I am trying to get the above to work using JScript. I am encountering an error on the command.execute(..) function call. (error '80004002' No such interface supported). Code I am using is as follows: oCmd.ActiveConnection = oConnection; oCmd.CommandStream = xmlDoc; oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"; oCmd.Properties("Output Stream") = xmlDoc2; oCmd.Execute(null,null,adExecuteStream); Any idea's???

# Retrieving XML in UTF-8 encoding 1/30/2003 10:24 AM non

I saw a demo which is using Stream (seats between Command and DOM) and set the Encoding property to "UTF-8" before having DOM read the XML string from the stream.

# No Output 2/7/2003 7:35 PM Paul

When I run your example I get nothing. No errors. No output. My sense is that the query is working because when I remove the where clause in my query it takes longer for the page to complete the request. However, when I inspect the oDOM object, there are no nodes. Likewise, writing directly to the Response object produces no output. When I use the same sql using a recordset object, I get back data. Any ideas?

# No Output 2/12/2003 11:44 AM Paul

Solution. There were two problems: 1. There were chr(11) (vertical tab) characters in my database output, which caused the oDom object to not parse. 2. The response object was in fact working. The browser was not getting a content-type: text/xml so I needed to look at the "source" in the browser. (Thanks for the sample!)

# DANI 2/18/2003 1:40 PM DANI

I am using FORXML on a win2000/SQL2000.I am using "FOR XML EXPLICIT". Why do I get this error ? SQLXML: Error on load XML result. Spack 3 of sql2000 its installed and w2000 sp3. Thank you

# How can I retrieve the results of a FOR XML query using ASP? 2/18/2003 1:42 PM

# Do I need to write to a table? 2/24/2003 8:30 AM Joey

I don't get any error, am I suppose to write the output to a table? Or I suppose to see the data when I run the codes?

# Retrieving XML in UTF-8 encoding 3/7/2003 6:10 AM shaun

I have used this to get the UTF in there: xmlDoc.loadxml(xml) Set oXMLProcessingInstructions = xmlDoc.createProcessingInstruction ("xml", "version='1.0' encoding='utf-8'") xmlDoc.insertBefore oXMLProcessingInstructions, xmlDoc.childNodes(0)

# JScript 3/10/2003 7:35 AM fewaf

fwefq

# No Output 4/9/2003 2:21 PM Andy

Im getting the same thing, but the source only contains the HTML tags and the type, there is no content. I am trying to execute the XML directly (without ASP files, etc). Is that why its not working? I assumed it would return the results as plain XML? Thanks for any assistance

# ASP.net 7/7/2003 12:54 PM DC

Could you give the same example for asp.net

# Retrieving XML in UTF-8 encoding 7/10/2003 10:15 AM Shon

Before you execute the command object, set the encoding in the response object like this Response.Write ""

# Problems connecting to provider 7/28/2003 9:25 AM Lucie

I have the following error when trying set the Output Stream property."Current provider does not support output streams." Can anybody help? Thanks!

# XML parsing error: Invalid at the top level of the document. 8/29/2003 2:25 PM vijaya

Hi, I am kind of new to using XML support for SQL. I am trying to use OPENXML to display an XML document as a resultset. The XML document I posses is Very large so I couldn't go for internal representation. Can any one suggest how to use external document name in sp_xml_preparedocument. I am running this query in query analyzer. -------------------------------- DECLARE @iDoc int DECLARE @XMLDoc varchar(20) SET @XMLDoc = 'C:\venkat\SampleContacts.xml' EXEC sp_xml_preparedocument @iDoc OUTPUT, @XMLDoc SELECT * FROM OPENXML(@iDOc, '/rs:data/z:row', 1) EXEC sp_xml_removedocument @iDoc ----------------- Thank you.

# Outputing the stream to a variable 9/2/2003 1:40 PM Greg

Yess Man !

# Retrieving XML in UTF-8 encoding 9/2/2003 1:42 PM Greg

Try this : set stm = Server.CreateObject("ADODB.Stream") With stm .CharSet = "UTF-8" .Open End With

# Retrieving XML in UTF-8 encoding 9/2/2003 1:44 PM Greg

Or this : With cmd .ActiveConnection = m_strConnectionString .CommandType = m_strCommandType .CommandText = m_strCommandText .Properties("Output Stream") = stm .Properties("Output Encoding") = m_strEncoding For me it worked with ISO-8859-1 and accents ! have fun !

# Do I need to write to a table? 10/1/2003 12:13 PM wrewr

werer

# Problems 10/2/2003 6:08 PM Brett

What you need to do, I figured this out the hard way, open IIS. Under the home directory tab you need to check the "Script source access". I found this out after some pretty hectic searching on MSDN

# No Output 10/15/2003 6:07 AM Mike

If you select view source on the output page you will see the result!

# re: How can I retrieve the results of a FOR XML query using ASP? 12/3/2003 6:24 AM Charlie

I am having a problem retrieving 800 records from a SQL server database it is taking over a minute (too long). I am using ADODB.Stream to retrieve the data from a stored procedure with the FOR XML clause. When I execute this in Query analyser is completes in 3 seconds.

Any ideas?

Cheers,

Charlie

(Junk@Redline<NO_SPAM_PLEASE>Software.co.uk
Remove the <NO_SPAM_PLEASE> :)

# re: How can I retrieve the results of a FOR XML query using ASP? 12/3/2003 8:43 AM Bryant

Are you loading the Stream into a DOMDocument or are you just returning it to the Response stream?

# re: Retrieving XML in UTF-8 encoding 12/17/2003 8:45 AM vamosb

This is the right code for me, but only if the charset of the html page what contains the input form is iso-8859-1.

[code]
Set Stream = Server.CreateObject("ADODB.Stream")
Stream.Type = 2 'adTypeText
Stream.Open

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = MyConnObject
oCmd.CommandText = sqlReport
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = Stream
oCmd.Properties("Output Encoding") = "utf-8"
oCmd.Execute , , 1024
Set oCmd = Nothing

Stream.Charset = "iso-8859-1"
Stream.Position = 0
oDom.loadXML(Stream.ReadText)

[/code]

If the charset of the input page is different, you should change the Charset of Stream.

vamosb@zuriel.hu

# re: How can I retrieve the results of a FOR XML query using ASP? 1/26/2004 2:39 AM moordi

i 've got the following error (file)
<?xml version="1.0" ?>
- <root>
<?MSSQLError HResult="0x80040e14" Source="Microsoft OLE DB Provider for SQL Server" Description=""?>
</root>
my sql query is as follow:
sSQL = "<?xml version='1.0'?>" & _
"<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
"SELECT 1 AS Tag, " & _
"NULL AS Parent, " & _
"Verkooporder.Verkoopordernr AS [Verkooporder!1!Verkoopordernr]," & _
"Verkooporder.Klantnr AS [Verkooporder!1!Leveranciernr!Element]," & _
"Verkooporder.OrderDatum AS [Verkooporder!1!Orderdatum!Element]," & _
"Verkooporder.Medewerkernr AS [Verkooporder!1!Medewerkernr!Element]," & _
"NULL AS [Verkooporderregel!2!Productnr]," & _
"NULL AS [Verkooporderregel!2!Aantal!Element]," & _
"NULL AS [Verkooporderregel!2!Verkoopprijs!Element]" & _
"FROM tbverkooporder Verkooporder" & _
"UNION ALL" & _
"SELECT 2, " & _
"1, " & _
"Verkooporder.Verkoopordernr," & _
"NULL," & _
"NULL," & _
"NULL," & _
"verkooporderregel.Productnr," & _
"verkooporderregel.Aantal," & _
"verkooporderregel.verkoopprijs" & _
"FROM tbverkooporder Verkooporder JOIN tbverkooporderregel Verkooporderregel" & _
"ON Verkooporder.verkoopordernr = Verkooporderregel.verkoopordernr" & _
"JOIN tbProducten Product" & _
"ON Verkooporderregel.productnr = Product.productnr" & _
"group by verkooporder.verkoopordernr,verkooporderregel

# re: How can I retrieve the results of a FOR XML query using ASP? 1/26/2004 2:41 AM moordi

verkooporderregel.Aantal,verkooporderregel.verkoopprijs" & _
"ORDER BY [Verkooporder!1!Verkoopordernr], TAG" & _
"FOR XML EXPLICIT</sql:query></root>"
its works in the query analyser

hio0521484@yahoo.com

# re: How can I retrieve the results of a FOR XML query using ASP? 6/3/2004 7:16 AM dzver

we had same problem with windows-1251 and utf8

our solution is:

<%@CODEPAGE=65001%>

# re: How can I retrieve the results of a FOR XML query using ASP? 11/17/2004 10:42 AM butthead

wow, that first guy, on the top is a freakin dumbass!

# re: How can I retrieve the results of a FOR XML query using ASP and saving to file 1/17/2005 12:53 PM jazmin

so once you get this to work for you, how do you pipe it to a file?

# re: How can I retrieve the results of a FOR XML query using ASP? 4/19/2005 5:37 PM Paul Wood

I and quite a few other people have found that this method hangs inconsistently when used on Windows Server 2003. I have a number of sites using this method that worked beautifully on Windows 2000 before upgrading, then they stop working. Sometimes it only takes a few hours before the site hangs, other times a few days. It hangs at this point (oCmd.Execute , , 1024) and will hang on identical queries that have worked before. No event is logged and no error message is displayed.

Sounds like some kind of memory leak or buffer filling to me. Can find no solution, have you heard of this and can you help?

# re: How can I retrieve the results of a FOR XML query using ASP? 4/19/2005 5:43 PM Paul Wood

Also, CPU usage is normal and the site can only be made to work again upon recycling the application pool. Here is another person with the problem.

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-xml/1241/For-XML-Problem-with-IIS6-and-W2k3

# re: How can I retrieve the results of a FOR XML query using ASP? 11/2/2005 4:15 PM Frank

got it working on my system; sql2000, on remote box (on network). prints out results as as transformed xml (via test3.xsl)

<%
Const adUseClient = 3
Dim adoConn

Set adoConn = Server.CreateObject("ADODB.Connection")

Dim sConn
sConn= "Provider=SQLOLEDB;Data Source=DEV;Initial Catalog=abc;Integrated Security=SSPI;"
adoConn.ConnectionString = sConn
adoConn.CursorLocation = adUseClient
adoConn.Open

Dim adoCmd
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoCmd.ActiveConnection = adoConn

Dim sQuery
sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT TOP 20 * FROM properties FOR XML AUTO, ELEMENTS</sql:query></ROOT>"
Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open
adoStreamQuery.WriteText sQuery, adWriteChar
adoStreamQuery.Position = 0
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
adoCmd.CommandStream = adoStreamQuery
adoCmd.Properties("Output Stream") = response
adoCmd.Properties("Output Encoding") = "utf-8"
adoCmd.Properties("xsl").Value = Server.MapPath("test3.xsl")

Response.ContentType = "text/xml"
Response.write "<?xml version=""1.0"" encoding=""ISO-8859-1""?>" & vbCrLf
response.write "<?xml-stylesheet type=""text/xsl"" href=""test3.xsl""?>" & vbCrLf
adoCmd.Execute , , 1024

Set adoCmd = Nothing
set adoStreamQuery = nothing
set adocmd = nothing
set adpConn = nothing
adoConn.close

# re: How can I retrieve the results of a FOR XML query using ASP? 5/15/2006 12:00 PM evol

If using ASP to write out your XML, change

objStream.Charset = "utf-8"
too
objStream.Charset = "ISO-8859-1"

Cheers...

- evol

# re: How can I retrieve the results of a FOR XML query using ASP? 9/28/2007 12:18 AM umashankar.TCS

Thnaks for your comments , through which i got solution ..cool...

# re: How can I retrieve the results of a FOR XML query using ASP? 3/22/2008 4:58 AM Haseeb

I have run the above code but on the line:
oCmd.Properties("Output Stream") = Response
The page displays the following error:
Error Type:
ADODB.Command (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
Is there any help about this Error Message?

# re: How can I retrieve the results of a FOR XML query using ASP? 11/6/2008 11:18 PM limar

I have a stored procedure which returns an XML value using FOR XML EXPLICIT.

Returned format is:
<test>
<key id='1' name='test1' value='Math' />
<key id='2' name='test2' value='Science' />
.
.
.
</test>

id is actually a uniqueidentifier.

This is my code:
Dim oCmd, oXML, oStream, oCon
Dim spParam

spParam = "Test"

Set oCmd = Server.CreateObject("ADODB.Command")
Set oStream = Server.CreateObject("ADODB.Stream")
Set oXML = Server.CreateObject("Msxml2.DOMDocument")
Set oCon = Server.CreateObject("ADODB.Connection")

oCon.Open "<my connection string here>"
oCmd.ActiveConnection = oCon
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "SPReturnTextXML '" & spParam & "'"

oStream.Open
oCmd.Properties("Output Stream") = oStream
oCmd.Execute , , adExecuteStream
oStream.Position = 0
oStream.Charset = "ISO-8859-1"

oXML.Async = False
oXML.LoadXML("<?xml version='1.0' encoding='ISO-8859-1'?>" & objStream.ReadText)

If oXML.parseError.errorCode <> 0 Then
Response.Write("Error: " & oXML.parseError.reason)
End If

''Here will be storing the oXML.xml to application variable
Application("TestXML") = oXML.xml

----------------------------------------------
----------------------------------------------

Error that will be encountered for "oCmd.Execute , , adExecuteStream" this line will be:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Syntax error or access violation

May I know the cause of this one?
May I know also on how to solve this problem?

Thank you in advance.

# re: How can I retrieve the results of a FOR XML query using ASP? 11/7/2008 2:44 AM limar

I have resolved the above issue by using your code, but when I change the sSQL value to my stored procedure it will generate an error during execute.

ERROR:
Microsoft XML Extensions to SQL Server (0x80040E14)
MSXML3: Invalid at the top level of the document

I use your code and save it to xml file, and then I executed my sproc using the SQL Management Studio, then click on the output and then save it as xml file.

Then I compared both file but there was no difference.

How come there is an error using my Sproc?

# re: How can I retrieve the results of a FOR XML query using ASP? 5/14/2009 2:22 AM johan

Hi Limar!

You need to append a closing and opening xml tag to the string that you receive...
Fore some strange reason, you get xml back without a top level element...
strXml="<result>"&strXml&"</result>"
should do the trick. supposing strXml is the string result that you receive.

# george 6/18/2009 3:31 AM george

BEDgkb bkwPpwvo03NXzw47jvGa

# george 6/18/2009 3:32 AM george

BEDgkb bkwPpwvo03NXzw47jvGa

# george 6/18/2009 3:33 AM george

BEDgkb bkwPpwvo03NXzw47jvGa

# george 6/18/2009 3:33 AM george

BEDgkb bkwPpwvo03NXzw47jvGa

Title
Name
Url
Comments
Remember Me?

FAQ #2

last updated:
8/16/2001

Did the information in this faq help answer your question?

Yes
No
Did Not Apply

Survey Results: 482

Yes

N/A


© 2001, 2002, 2003 sqlxml.org