Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

FOR XML SQL Server output

Status
Not open for further replies.

travisbrown

Technical User
Dec 31, 2001
1,016
I'm trying to return XML output out of SQL Server 2000, but just getting nothing - just the literal top level tags specified below. I'm not sure where I'm going wrong. Any hints? The query runs fine in QA.

Code:
sSQL = "SELECT ad_image AS src, ad_url as url FROM t_ads item FOR XML AUTO"

	dim objStream
	dim objConn
	dim objComm
	dim objXML
	set objStream = Server.CreateObject("ADODB.Stream")
	set objConn = Server.CreateObject("ADODB.Connection")
	set objComm = Server.CreateObject("ADODB.Command")
	set objXML = Server.CreateObject("MSXML2.DOMDocument")
	objConn.Open dbconn
	objComm.ActiveConnection = objConn
	objComm.CommandType = 8
	objComm.CommandText = sSQL
	objStream.Open
	objComm.Properties("Output Stream").Value = objStream
	objComm.Execute ,, adExecuteStream
	objStream.Position = 0
	objXML.LoadXML("<ads>" &objStream.ReadText & "</ads>")
	if objXML.parseError.errorCode <> 0 then
	 Response.Write "Error loading XML: " & objXML.parseError.reason
	 Response.End
	end if
	Response.ContentType = "text/xml"
	Response.Write objXML.xml

Output
Code:
<ads></ads>
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top