saving recordset to XML file

S

shank

On this page...
http://www.w3schools.com/ado/met_rs_save.asp
....it describes how to save a recordset to file with the following...

You can save a Recordset in XML format:

<%
set xmlDoc=CreateObject("Microsoft.XMLDOM")
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Customers", conn
'Save the Recordset into a DOM tree
rs.Save xmldoc, 1
%>

What I don't get is where does one stipulate a filename? I put the above
code in my page and it appeared to load and execute without error. But no
file.

thanks
 
B

Bob Barrows [MVP]

shank said:
On this page...
http://www.w3schools.com/ado/met_rs_save.asp
...it describes how to save a recordset to file with the following...

You can save a Recordset in XML format:

<%
set xmlDoc=CreateObject("Microsoft.XMLDOM")
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Customers", conn
'Save the Recordset into a DOM tree
rs.Save xmldoc, 1
%>

What I don't get is where does one stipulate a filename?
Click to expand...
This script does not describe how to save a recordset to file - you put
words in its "mouth".
All it does is stream the recordset to xmldoc, which is a domdocument
stored in memory. To save to a file, you have to substitute a file name
for xmldoc, and it has to be in a location where the user has Modify
permissions. Like this:

file=server.mappath("xmlfiles/mynewxmlfile.xml")
rs.Save file, 1
 
S

shank

Bob Barrows said:
This script does not describe how to save a recordset to file - you put
words in its "mouth".
All it does is stream the recordset to xmldoc, which is a domdocument
stored in memory. To save to a file, you have to substitute a file name
for xmldoc, and it has to be in a location where the user has Modify
permissions. Like this:

file=server.mappath("xmlfiles/mynewxmlfile.xml")
rs.Save file, 1
Click to expand...
= = = = = = = = == = = = = = = = == = = = = = = = =
Below is my entire page. I do get records to the screen, but no file is
saved. I gave full permissions to this folder: C:\XMLData. Nothing is
written. What did I miss?
thanks


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/SI.asp" -->
<%
Dim rsProduct
Dim rsProduct_numRows
set xmlDoc=CreateObject("Microsoft.XMLDOM")
Set rsProduct = Server.CreateObject("ADODB.Recordset")
rsProduct.ActiveConnection = MM_SI_STRING
rsProduct.Source = "{call p2005.stp_TC_XML}"
rsProduct.CursorType = 0
rsProduct.CursorLocation = 2
rsProduct.LockType = 1
rsProduct.Open()

rsProduct_numRows = 0
%>
<%
xmldata=server.mappath("C:\XMLData\Products.xml")
rsProduct.Save xmldata, 1
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsProduct_numRows = rsProduct_numRows + Repeat1__numRows
%>
<html>
<body>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsProduct.EOF))
%>
<%=(rsProduct.Fields.Item("OrderNo").Value)%>&nbsp;<%=(rsProduct.Fields.Item("Label").Value)%><br>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsProduct.MoveNext()
Wend
%>

</body>
</html>
<%
rsProduct.Close()
Set rsProduct = Nothing
%>
 
B

Bob Barrows [MVP]

shank said:
= = = = = = = = == = = = = = = = == = = = = = = = =
Below is my entire page. I do get records to the screen, but no file
is saved. I gave full permissions to this folder: C:\XMLData. Nothing
is written. What did I miss?
thanks


<%
xmldata=server.mappath("C:\XMLData\Products.xml")
Click to expand...

?? Why use mappath with a true filesystem path?
rsProduct.Save xmldata, 1
Click to expand...

Are you thinking that this code will write to the user's C drive? It
won't.
Are you looking at C:\XMLData\ on the server?

If that's not it, make sure you don't have On Error Resume Next masking
any errors.
 
S

shank

Bob Barrows said:
?? Why use mappath with a true filesystem path?


Are you thinking that this code will write to the user's C drive? It
won't.
Are you looking at C:\XMLData\ on the server?

If that's not it, make sure you don't have On Error Resume Next masking
any errors.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
=================================================
I changed the server.mappath and I can now write a file to the server C
drive. Thanks!

To add another twist, I have a stored procedure that uses FOR XML AUTO,
ELEMENTS and it displays the data just the way I prefer when run in a query
in Management Studio. How do I get those exact results into a file using
ASP? It's not like it's many records. It's one huge record.

thanks
 
B

Bob Barrows [MVP]

shank said:
To add another twist, I have a stored procedure that uses FOR XML
AUTO, ELEMENTS and it displays the data just the way I prefer when
run in a query in Management Studio. How do I get those exact results
into a file using ASP? It's not like it's many records. It's one huge
record.
Click to expand...
For that, you will have to use an ADO Stream object. I don't have time
right now, l but if you google "Stream" and "FOR XML", you should be
able to find the examples I posted a while back.
 
S

shank

Bob Barrows said:
For that, you will have to use an ADO Stream object. I don't have time
right now, l but if you google "Stream" and "FOR XML", you should be
able to find the examples I posted a while back.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
================================================
I found what I believe you authored. Tried adapting to what I have below. I
don't get any results to screen. Does the below look familiar? Where did I
screw up?
thanks!

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/SI.asp" -->
<%
dim xmldoc
Const adExecuteStream = &H00000400
Const adCmdText = &H0001
Set xmldoc=server.createobject("msxml2.domdocument")
%>
<%
Dim rsProduct
Dim rsProduct_numRows
Set rsProduct = Server.CreateObject("ADODB.Recordset")
rsProduct.ActiveConnection = MM_SI_STRING
rsProduct.Source = "{call call p2005.stp_TC_XML}"
rsProduct.CursorType = 0
rsProduct.CursorLocation = 2
rsProduct.LockType = 1
rsProduct.Open()

rsProduct_numRows = 0
%>
<%
rsProduct.Properties("xml root").Value = "root"
rsProduct.Properties("Output Stream") = xmldoc
rsProduct.Execute , , adExecuteStream + adCmdText
%>

<html>
<XML id="xmlData">
<%=xmldoc.xml%>
</XML>
</html>
<%
rsProduct.Close()
Set rsProduct = Nothing
%>
<%
set cmd=nothing
cn.close
set cn=nothing
%>
 
B

Bob Barrows [MVP]

shank said:
================================================
I found what I believe you authored. Tried adapting to what I have
below. I don't get any results to screen. Does the below look
familiar? Where did I screw up?
thanks!

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/SI.asp" -->
<%
dim xmldoc
Const adExecuteStream = &H00000400
Const adCmdText = &H0001
Set xmldoc=server.createobject("msxml2.domdocument")
%>
<%
Dim rsProduct
Dim rsProduct_numRows
Set rsProduct = Server.CreateObject("ADODB.Recordset")
rsProduct.ActiveConnection = MM_SI_STRING
rsProduct.Source = "{call call p2005.stp_TC_XML}"
Click to expand...

ALWAYS USE AN EXPLICIT CONNECTION OBJECT!!

This does not look like my code. For one thing, my code does not use a
recordset. Here is how to stream to a dom document:

dim cn, xmldoc,sQuery, cmd
Const adExecuteStream = &H00000400
Const adCmdStoredProc = &H0004
set cn=CreateObject("adodb.connection")
cn.open MM_SI_STRING
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "p2005.stp_TC_XML"
cmd.CommandType=adCmdStoredProc
cmd.Properties("xml root").Value = "root"
Set xmldoc=CreateObject("msxml2.domdocument")
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText
cn.close: set cn=nothing
set cmd=nothing
<html>
<XML id="xmlData">
<%=xmldoc.xml%>
</XML>
</html>
 
S

shank

Your help is greatly appreciated! 2 issues arise now.

1) The results written to screen do not include the element names and
structure like the query. It's continuous text. I assume they wouldn't be
included in any file save as well.

2) When I tried saving the stream to a file with: cmd.Save, I get the error
that it's not supported. Obviously, the save to file is different between
recordsets and commands.

The whole idea of this is generate XML files our customers can download and
import into their own database or system.

thanks!

<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include file="../Connections/SI.asp" -->
<%
dim cn, xmldoc,sQuery, cmd, adCmdText, xmldata
Const adExecuteStream = &H00000400
Const adCmdStoredProc = &H0004
set cn=CreateObject("adodb.connection")
cn.open MM_SI_STRING
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "p2005.stp_TC_XML"
cmd.CommandType=adCmdStoredProc
cmd.Properties("xml root").Value = "root"
Set xmldoc=CreateObject("msxml2.domdocument")
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText

xmldata="C:\XMLData\Products.xml"
cmd.Save xmldata, 1

cn.close: set cn=nothing
set cmd=nothing
%>
<html>
<XML id="xmlData">
<%=xmldoc.xml%>
</XML>
</html>
 
B

Bob Barrows [MVP]

shank said:
Your help is greatly appreciated! 2 issues arise now.

1) The results written to screen do not include the element names and
structure like the query. It's continuous text. I assume they
wouldn't be included in any file save as well.
Click to expand...

Huh? You asked for XML ...
There are options you can set with FOR XML - see SQL Books OnLine.
2) When I tried saving the stream to a file with: cmd.Save, I get the
error that it's not supported. Obviously, the save to file is
different between recordsets and commands.
Click to expand...

"cmd" is a Command object, not a recordset! It has no Save method. Don't you
have access to the documentation?
Here's the msdn documentation:
http://msdn2.microsoft.com/en-us/library/ms675532.aspx

You can save the dom document to file using the dom document's Save method:

xmldoc.Save filename
 
A

Anthony Jones

shank said:
Your help is greatly appreciated! 2 issues arise now.

1) The results written to screen do not include the element names and
structure like the query. It's continuous text. I assume they wouldn't be
included in any file save as well.

2) When I tried saving the stream to a file with: cmd.Save, I get the error
that it's not supported. Obviously, the save to file is different between
recordsets and commands.

The whole idea of this is generate XML files our customers can download and
import into their own database or system.

thanks!

<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include file="../Connections/SI.asp" -->
<%
dim cn, xmldoc,sQuery, cmd, adCmdText, xmldata
Const adExecuteStream = &H00000400
Const adCmdStoredProc = &H0004
set cn=CreateObject("adodb.connection")
cn.open MM_SI_STRING
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "p2005.stp_TC_XML"
cmd.CommandType=adCmdStoredProc
cmd.Properties("xml root").Value = "root"
Set xmldoc=CreateObject("msxml2.domdocument")
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText

xmldata="C:\XMLData\Products.xml"
cmd.Save xmldata, 1

cn.close: set cn=nothing
set cmd=nothing
Click to expand...

Delete the below
%>
<html>
<XML id="xmlData">
<%=xmldoc.xml%>
</XML>
</html>
Click to expand...

The code above places the XML in the output as if it is HTML. Most if not
all the tag names in the XML are going to be meaningless to HTML and are
ignored. This just leaves the element text as displayable.

Use this instead:-

Response.ContentType = "text/xml"
Response.Charset = "UTF-8" 'If <?xml is included in the dom with a different
encoding use that.
xmldoc.save Response
%>
 
A

Anthony Jones

Bob Barrows said:
ALWAYS USE AN EXPLICIT CONNECTION OBJECT!!

This does not look like my code. For one thing, my code does not use a
recordset. Here is how to stream to a dom document:

dim cn, xmldoc,sQuery, cmd
Const adExecuteStream = &H00000400
Const adCmdStoredProc = &H0004
set cn=CreateObject("adodb.connection")
cn.open MM_SI_STRING
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "p2005.stp_TC_XML"
cmd.CommandType=adCmdStoredProc
cmd.Properties("xml root").Value = "root"
Set xmldoc=CreateObject("msxml2.domdocument")
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText
cn.close: set cn=nothing
set cmd=nothing
<html>
<XML id="xmlData">
<%=xmldoc.xml%>
</XML>
</html>
Click to expand...

Bob,

Would you happen to know off hand which version of ADODB is needed to
support the above code?
 
B

Bob Barrows [MVP]

Anthony said:
Bob,

Would you happen to know off hand which version of ADODB is needed to
support the above code?
Click to expand...

Sorry, OTOMH, I can only say that it's been supported for "a very long
time". :) The code snip I posted came from a post I made in 2003, if that
helps.

Oh wait! That code snip was adapted from a SQL 2000 BOL article! So, what
version of ADO was "current" for SQL2000 ... ? Let's see ...
"These features use ADO 2.6."

So, at least since ADO 2.6 would have to be my answer for this question,
although I would be surprised if it failed with 2.5
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

In javascript, XML File Create, File Save 2
ADO Recordset Save as XML error 0
ADO Recordset Save as XML error 0
recordset 5
ASP Access Recordset Paging 6
Recordset problem 8
Please help - trying to return XML from a recordset 6
xml transform to excel 0

Members online

Total: 60 (members: 1, guests: 59)
Robots: 429

Forum statistics

Threads
473,969
Messages
2,570,161
Members
46,705
Latest member
Stefkari24

Latest Threads

Top