saving recordset to XML file

Discussion in 'ASP General' started by shank, Sep 4, 2007.

  1. shank

    shank Guest

    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
     
    shank, Sep 4, 2007
    #1
    1. Advertising

  2. 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.
     
    Bob Barrows [MVP], Sep 4, 2007
    #2
    1. Advertising

  3. shank

    shank Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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"%>
    <!--#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
    %>
     
    shank, Sep 4, 2007
    #3
  4. 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.
     
    Bob Barrows [MVP], Sep 4, 2007
    #4
  5. shank

    shank Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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
     
    shank, Sep 4, 2007
    #5
  6. 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.
     
    Bob Barrows [MVP], Sep 4, 2007
    #6
  7. shank

    shank Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:OLe%...
    > 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"%>
    <!--#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
    %>
     
    shank, Sep 4, 2007
    #7
  8. shank wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:OLe%...
    >> 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"%>
    > <!--#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}"


    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>

    --
    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"
     
    Bob Barrows [MVP], Sep 5, 2007
    #8
  9. shank

    shank Guest

    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>
     
    shank, Sep 5, 2007
    #9
  10. 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"
     
    Bob Barrows [MVP], Sep 5, 2007
    #10
  11. "shank" <> wrote in message
    news:Oy$...
    > 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


    Delete the below

    > %>
    > <html>
    > <XML id="xmlData">
    > <%=xmldoc.xml%>
    > </XML>
    > </html>
    >


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

    --
    Anthony Jones - MVP ASP/ASP.NET
     
    Anthony Jones, Sep 6, 2007
    #11
  12. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > shank wrote:
    > > "Bob Barrows [MVP]" <> wrote in message
    > > news:OLe%...
    > >> 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"%>
    > > <!--#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}"

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


    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
     
    Anthony Jones, Sep 6, 2007
    #12
  13. 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"
     
    Bob Barrows [MVP], Sep 6, 2007
    #13
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Kandell
    Replies:
    4
    Views:
    4,205
    eeebop
    Dec 10, 2004
  2. Luis Esteban Valencia
    Replies:
    0
    Views:
    2,545
    Luis Esteban Valencia
    Jan 6, 2005
  3. Michael Haberfellner

    Webservice returns recordset as XML-File

    Michael Haberfellner, Aug 9, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    348
    =?Utf-8?B?Y3NoYXJwZXI=?=
    Aug 9, 2007
  4. Hung Huynh
    Replies:
    8
    Views:
    341
    Bob Barrows
    Sep 24, 2003
  5. Lovely Angel For You

    Saving Images While Saving ASP Pages !

    Lovely Angel For You, Oct 2, 2003, in forum: ASP General
    Replies:
    1
    Views:
    222
    Curt_C [MVP]
    Oct 3, 2003
Loading...

Share This Page