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ł
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
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
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
%>
