Search code examples
sql-serverxmlvbscriptasp-classic

ASP Classic SQL Server return results from database in XML format

I'm trying to return results from query in ASP, it's working when I use something like Response.write Recordset(0) but it's not working in xml format. Here is my code:

Dim conn
Dim Recordset
Dim xmlDoc 

Set conn = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM dbo.myTable for xml auto;"

conn.Open "Provider=SQLOLEDB; Data Source = myPC\SQLEXPRESS;
Initial Catalog = peopleDatabase; User Id = rafalsonn; Password=xxx"
Recordset.Open SQL,conn
Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
Recordset.Save xmlDoc,1

Recordset.Close
Set Recordset=nothing
conn.Close
Set conn=nothing
Response.Write xmlDoc.xml

And the result:

This XML file does not appear to have any style information associated with it. The document tree is shown below.

I've tried to make it work for a long time, but I don't know where the bug is. Greetings, Rafał

Solution

  • And here is another way.

    Printing XML output of the Recordset directly to the Response, in ADO's XML Persistence Format.

    <%
Response.ContentType = "text/xml"

Const adPersistXML = 1
Dim conn
Dim Recordset

Set conn = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM dbo.myTable"
conn.Open "Provider=SQLOLEDB; Data Source = myPC\SQLEXPRESS; Initial Catalog = peopleDatabase; User Id = rafalsonn; Password=xxx"
Recordset.Open SQL,conn, 1, 1
Recordset.Save Response, adPersistXML 
%>

    Related Links: