Problem Converting ADO Recordset to XML

[CHRISTOS STAVRINOU]

I am a student in Athens University on informatics department and I study XML. My problem is that I can't persist my data(My Recordset) to a file or a stream. I want to convert a Recordset of a database to an XML file or to a stream with the contents of recordset to XML format. I have found the following code to convert a Recordset to XML file.

 

Peristing to a File

 

<%@ LANGUAGE="VBSCRIPT" %>
<%
Dim rsTitles
Dim connNorthwind
Dim szFile

Set connNorthwind = Server.CreateObject("ADODB.Connection")
connNorthwind.open "provider=sqloledb; data source=Northwind; initial catalog=pubs; user id=sa;password="
Set rsTitles = Server.CreateObject("ADODB.Recordset")
rsTitles.CursorLocation = adUseClient
rsTitles.Open "SELECT * FROM titles", connNorthwind
szFile = Server.MapPath("testfile.xml")
rsTitles.Save szFile, adPersistXML   'saves file in XML format
'rsTitles.Save szFile                'saves file in ADTG (the default) format

rsTitles.Close
connNorthwind.Close

Set rsTitles = Nothing
Set connNorthwind = Nothing
%>

 

The following line

rsTitles.CursorLocation = adUseClient

generates an error as not supported command. I tried to delete this line and all the script code is executed with no problems but the results are not correct. I used my own database and SQL Command when I tried to execute this script. I tried with SQL Server and MS Access(MS Jet Engine 4) after deleting this unsupported command. Then I take the XML file testfile.xml but the contents of the file are rubbish and not a well formed XML file. Then I tried the following code but I still  have the same problems. The command rsTitles.CursorLocation = adUseClient is not supported. I tried to use adUseServer but also is not suppored. Deleting this line the script code is executed but Internet Explorer displays an error message that is not a well formed XML (XML with errors). I believe that the contents of the stream file are rubish as the previous code in the testfile.xml

 

Peristing to Streams

 

<%@ LANGUAGE="VBSCRIPT" %>
<%
Dim rsTitles
Dim connNorthwind
Dim strmSaveXML

Set connNorthwind = Server.CreateObject("ADODB.Connection")
connNorthwind.open "provider=sqloledb; data source=Northwind; initial catalog=pubs; user id=sa;password="

Set rsTitles = Server.CreateObject("ADODB.Recordset")
Set strmSaveXML = Server.CreateObject("ADODB.Stream")

rsTitles.CursorLocation = adUseClient
rsTitles.open "SELECT * FROM titles", connNorthwind
rsTitles.Save strmSaveXML, adPersistXML
rsTitles.Close
connNorthwind.Close
Set connNorthwind = Nothing

'ReOpen File
rs.Open strmSaveXML
%>

 

 

Please help me If you have any answers to my question or If you have any other ideas how I can perform this operation of converting a Recordset to an XML file or to an XML stream, and who I can made the same thing with the reverse order, how to convert the contents of an XML file or stream to an ADO Recordset. It is very important for me to solve this problem because it is the main part of my degree exercise.

(I have windows 2000 with SP1, my web server is Internet Information Services of Windows 2000, MS SQL Server 7, MS ADO 2.5 SP1 2.51.5303.5, IE 5.5 with SP1 as my web browser).

 

I will check news groups for any answers but it will be easier to me if you also email me directly.

Email: stav...@hotmail.com

 

Thank You

Christos Stavrinou