ASP Classic SQL Server return results from databas

Question

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ł

Answer 1

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:

• Save Method
• Persisting Records in XML Format
• XML Persistence Format

Answer 2

Set CursorLocation and CursorType like:

<%@ Page Language="VB" AspCompat="true" %>
<%
Dim rs as Object = Server.CreateObject("ADODB.Recordset")
Dim cn as Object = Server.CreateObject("ADODB.Connection")
Dim xml as Object = Server.CreateObject("MSXML2.DomDocument.6.0")

Dim sql as String = "select * from table1 where rownum < 10"

cn.Open ("Provider=OraOLEDB.Oracle;Data Source=server1/inst1;User ID=user1;Password=password1;")

rs.CursorLocation = 3 'adUseClient
rs.CursorType = 3 'adOpenStatic
rs.LockType = 1 'adLockReadOnly
rs.Open (sql, cn)
rs.Save (xml,1)

Response.ContentType="text/xml"
Response.Write("<?xml version='1.0' encoding='utf-8'?>")
Response.Write(xml.xml )

cn.Close

%>

Answer 3

Here's how I achieved this

<% 
Set oXML = Server.CreateObject("MSXML2.DomDocument.6.0")
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=localhost\SQLEXPRESS;User ID=myuserid;Initial Catalog=mydatabase;Password=mypassword"
oCmd.Properties("Output Stream") = oXML
oXml.async = false
oCmd.CommandText = "SELECT id, Headline, Author, Publication, Pubdate, Intro from Articles as Article for xml auto, elements, root('Articles')"
oCmd.Execute , , 1025
Response.ContentType="text/xml"
Response.Write("<?xml version='1.0' encoding='utf-8'?>")
Response.Write oXML.xml 
Set oXML = Nothing
Set oCmd = Nothing
%>

'root('Articles')' puts the xml output inside <Articles>...</Articles>

This MSDN page shows what 'elements' does https://msdn.microsoft.com/en-us/library/ms188273.aspx