Converting database to xml

[levuhao]

I have a code segment in a asp page for retrieving a xml document from a database as follow:
1. dim rs
2. dim doc
3. dim cn
4. dim searchField, withField

' Create Objects
5. set cn = server.CreateObject("ADODB.Connection&quot
6. set rs = server.CreateObject("ADODB.Recordset&quot
7. set doc = server.CreateObject("MSXML2.DomDocument&quot

' Open Connection
8. cn.Open "CDLibrary;", "sa", "sqlserver2000"

' Get required data
9. rs.Open "select * from CD", cn, 0, 3

' Save Recordset to DOMDoc as XML
10. rs.Save doc, adPersistXML

' Set Response property
11. Response.ContentType = "text/xml"

' Stylesheet for browser display
12. Response.Write &quot;<?xml:stylesheet type=&quot;&quot;text/xsl&quot;&quot; href=&quot;&quot;CDList.xsl&quot;&quot;?>&quot; & vbcrlf

13. Response.Write doc.xml

At 10th line, why the doc is still empty after saving? Can who help me?

 

[Vince]

Hi,

I don't think that you can save a recordset striagt into an xml dom document.

The way that we initially got around this was to save the xml into an ado stream

dim s as new adodb.stream

rs.save s,adopersistxml

then load the data into the DOM document

xmlDoc.loadXML(s)

However we found a better way in the end.

Using SQL2000 we wrote stored proceedures which returned the data as XML using open XML

this example stored proceedure from microsoft shows how

SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO

simply set your dom document.loadxml(query return)

hope this is of use