saving recordset to XML file

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

Re: saving recordset to XML file

shank wrote:
> 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?
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

--
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.

Re: saving recordset to XML file

"Bob Barrows [MVP]" wrote in message
news:OwYo7uy7HHA.4660@TK2MSFTNGP02.phx.gbl...
> shank wrote:
>> 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?
> 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
>
= = = = = = = = == = = = = = = = == = = = = = = = =
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"%>

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


<%
While ((Repeat1__numRows <> 0) AND (NOT rsProduct.EOF))
%>
<%=(rsProduct.Fields.Item("OrderNo").Value)%> <%=(rsProduct.Fields.Item("Label").Value)%>

<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsProduct.MoveNext()
Wend
%>



<%
rsProduct.Close()
Set rsProduct = Nothing
%>

Re: saving recordset to XML file

shank wrote:
> = = = = = = = = == = = = = = = = == = = = = = = = =
> 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")

?? Why use mappath with a true filesystem path?

> rsProduct.Save xmldata, 1

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.

Re: saving recordset to XML file

"Bob Barrows [MVP]" wrote in message
news:uRZYYMz7HHA.5316@TK2MSFTNGP04.phx.gbl...
> shank wrote:
>> = = = = = = = = == = = = = = = = == = = = = = = = =
>> 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")
>
> ?? Why use mappath with a true filesystem path?
>
>> rsProduct.Save xmldata, 1
>
> 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.
=================================================
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

Re: saving recordset to XML file

shank wrote:
> 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.
>
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.

Re: saving recordset to XML file

"Bob Barrows [MVP]" wrote in message
news:OLe%23Hzz7HHA.5980@TK2MSFTNGP04.phx.gbl...
> shank wrote:
>> 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.
>>
> 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.
================================================
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"%>

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



<%=xmldoc.xml%>


<%
rsProduct.Close()
Set rsProduct = Nothing
%>
<%
set cmd=nothing
cn.close
set cn=nothing
%>

Re: saving recordset to XML file

shank wrote:
> "Bob Barrows [MVP]" wrote in message
> news:OLe%23Hzz7HHA.5980@TK2MSFTNGP04.phx.gbl...
>> shank wrote:
>>> 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.
>>>
>> 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.
> ================================================
> 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"%>
>
> <%
> 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}"

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


<%=xmldoc.xml%>



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: saving recordset to XML file

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

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


<%=xmldoc.xml%>

Re: saving recordset to XML file

shank wrote:
> 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.

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.

"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



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: saving recordset to XML file

"shank" wrote in message
news:Oy$6ky17HHA.1208@TK2MSFTNGP05.phx.gbl...
> 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 %>
>
> <%
> 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

Delete the below

> %>
>
>
> <%=xmldoc.xml%>
>
>
>

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 encoding use that.
xmldoc.save Response
%>

--
Anthony Jones - MVP ASP/ASP.NET

Re: saving recordset to XML file

"Bob Barrows [MVP]" wrote in message
news:eSTMNp07HHA.5184@TK2MSFTNGP03.phx.gbl...
> shank wrote:
> > "Bob Barrows [MVP]" wrote in message
> > news:OLe%23Hzz7HHA.5980@TK2MSFTNGP04.phx.gbl...
> >> shank wrote:
> >>> 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.
> >>>
> >> 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.
> > ================================================
> > 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"%>
> >
> > <%
> > 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}"
>
> 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
>
>
> <%=xmldoc.xml%>
>
>
>

Bob,

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


--
Anthony Jones - MVP ASP/ASP.NET

Re: saving recordset to XML file

Anthony Jones wrote:
>
> Bob,
>
> Would you happen to know off hand which version of ADODB is needed to
> support the above code?

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


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"