SP returning XML

Discussion in 'ASP General' started by Peter Morris, Oct 14, 2004.

  1. Peter Morris

    Peter Morris Guest

    Can someone give me an example of how to use a
    stored procedure that returns XML data?

    I want to call the SP from a VBScript program,
    and put the XML into a data island in the generated HTML.
     
    Peter Morris, Oct 14, 2004
    #1
    1. Advertising

  2. Peter Morris wrote:
    > Can someone give me an example of how to use a
    > stored procedure that returns XML data?
    >
    > I want to call the SP from a VBScript program,
    > and put the XML into a data island in the generated HTML.


    Do you mean a procedure that runs a query containing a FOR XML clause?

    Bob Barrows

    --
    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], Oct 14, 2004
    #2
    1. Advertising

  3. Peter Morris

    Peter Morris Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > Peter Morris wrote:
    >> Can someone give me an example of how to use a
    >> stored procedure that returns XML data?
    >>
    >> I want to call the SP from a VBScript program,
    >> and put the XML into a data island in the generated HTML.

    >
    > Do you mean a procedure that runs a query containing a FOR XML clause?
    >
    > Bob Barrows


    Yes, my SP has FOR XML AUTO, ELEMENTS. It returns the results
    as XML.

    Now, I have a VBscript program that dynamically generates HTML
    output. I want to run my SP from VBScript, and insert the XML as
    a data island in my HTML.
     
    Peter Morris, Oct 14, 2004
    #3
  4. Peter Morris wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:%...
    >> Peter Morris wrote:
    >>> Can someone give me an example of how to use a
    >>> stored procedure that returns XML data?
    >>>
    >>> I want to call the SP from a VBScript program,
    >>> and put the XML into a data island in the generated HTML.

    >>
    >> Do you mean a procedure that runs a query containing a FOR XML
    >> clause? Bob Barrows

    >
    > Yes, my SP has FOR XML AUTO, ELEMENTS. It returns the results
    > as XML.
    >
    > Now, I have a VBscript program that dynamically generates HTML
    > output. I want to run my SP from VBScript, and insert the XML as
    > a data island in my HTML.



    I have some code somewhere that uses a Stream to get te XML. Let me look for
    it: ... ah! Here it is:

    <%@ Language=VBScript %>
    <%
    option explicit
    dim cn, rs,sQuery, cmd, xmldoc
    Const adExecuteStream = &H00000400
    Const adCmdText = &H0001
    set cn=server.CreateObject("adodb.connection")
    cn.open "provider=sqloledb;data source=????;" & _
    "user id = ???; password=????;initial catalog=northwind"
    Set xmldoc=server.createobject("msxml2.domdocument")
    Set cmd = Server.CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = cn
    sQuery = "SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML auto"
    cmd.CommandText = sQuery
    cmd.Properties("xml root").Value = "root"
    cmd.Properties("Output Stream") = xmldoc
    cmd.Execute , , adExecuteStream + adCmdText
    set cmd=nothing
    cn.close
    set cn=nothing
    %>
    <html>
    ....
    <XML id="xmlData">
    <%=xmldoc.xml%>
    </XML>
    </html>

    HTH,
    Bob Barrows
    --
    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], Oct 14, 2004
    #4
  5. Peter Morris

    ajsmith02 Guest

    Hello Bob,
    I have posted my following issue in the sqlserver newsgroup. I have been
    using similar code for about 3 years on and IIS/W2K platform. It has
    performed flawlessly.

    We bought a new IIS6/W2K3 box and the same code hangs at this line of code
    (borrowed from your sample):
    cmd.Execute , , adExecuteStream + adCmdText
    We can go days without a problem and then all of a sudden whamo!!! I ran
    iisstate against one of the w3_wp.exe and it appears to be hanging when
    loading
    mlang.dll. Any help that you can lend me would be greatly appreciated.

    Thanks in advance.




    "Bob Barrows [MVP]" wrote:

    > Peter Morris wrote:
    > > "Bob Barrows [MVP]" <> wrote in message
    > > news:%...
    > >> Peter Morris wrote:
    > >>> Can someone give me an example of how to use a
    > >>> stored procedure that returns XML data?
    > >>>
    > >>> I want to call the SP from a VBScript program,
    > >>> and put the XML into a data island in the generated HTML.
    > >>
    > >> Do you mean a procedure that runs a query containing a FOR XML
    > >> clause? Bob Barrows

    > >
    > > Yes, my SP has FOR XML AUTO, ELEMENTS. It returns the results
    > > as XML.
    > >
    > > Now, I have a VBscript program that dynamically generates HTML
    > > output. I want to run my SP from VBScript, and insert the XML as
    > > a data island in my HTML.

    >
    >
    > I have some code somewhere that uses a Stream to get te XML. Let me look for
    > it: ... ah! Here it is:
    >
    > <%@ Language=VBScript %>
    > <%
    > option explicit
    > dim cn, rs,sQuery, cmd, xmldoc
    > Const adExecuteStream = &H00000400
    > Const adCmdText = &H0001
    > set cn=server.CreateObject("adodb.connection")
    > cn.open "provider=sqloledb;data source=????;" & _
    > "user id = ???; password=????;initial catalog=northwind"
    > Set xmldoc=server.createobject("msxml2.domdocument")
    > Set cmd = Server.CreateObject("ADODB.Command")
    > Set cmd.ActiveConnection = cn
    > sQuery = "SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML auto"
    > cmd.CommandText = sQuery
    > cmd.Properties("xml root").Value = "root"
    > cmd.Properties("Output Stream") = xmldoc
    > cmd.Execute , , adExecuteStream + adCmdText
    > set cmd=nothing
    > cn.close
    > set cn=nothing
    > %>
    > <html>
    > ....
    > <XML id="xmlData">
    > <%=xmldoc.xml%>
    > </XML>
    > </html>
    >
    > HTH,
    > Bob Barrows
    > --
    > 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"
    >
    >
    >
     
    ajsmith02, Oct 14, 2004
    #5
  6. ajsmith02 wrote:
    > Hello Bob,
    > I have posted my following issue in the sqlserver newsgroup. I have
    > been using similar code for about 3 years on and IIS/W2K platform.
    > It has performed flawlessly.
    >
    > We bought a new IIS6/W2K3 box and the same code hangs at this line of
    > code (borrowed from your sample):
    > cmd.Execute , , adExecuteStream + adCmdText
    > We can go days without a problem and then all of a sudden whamo!!! I
    > ran iisstate against one of the w3_wp.exe and it appears to be
    > hanging when loading
    > mlang.dll. Any help that you can lend me would be greatly
    > appreciated.
    >


    I wish I could help, but my company has not moved to that platform as yet.
    If you cannot find anything in the KB, then i suggest you open a case with
    MS Product Support. If it turns out to be a bug, you won't be charged for
    the call.

    Bob Barrows

    --
    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], Oct 14, 2004
    #6
  7. Peter Morris

    Peter Morris Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...

    >
    >
    > I have some code somewhere that uses a Stream to get te XML. Let me look
    > for it: ... ah! Here it is:
    >
    > <%@ Language=VBScript %>
    > <%
    > option explicit
    > dim cn, rs,sQuery, cmd, xmldoc
    > Const adExecuteStream = &H00000400
    > Const adCmdText = &H0001
    > set cn=server.CreateObject("adodb.connection")
    > cn.open "provider=sqloledb;data source=????;" & _
    > "user id = ???; password=????;initial catalog=northwind"
    > Set xmldoc=server.createobject("msxml2.domdocument")
    > Set cmd = Server.CreateObject("ADODB.Command")
    > Set cmd.ActiveConnection = cn
    > sQuery = "SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML auto"
    > cmd.CommandText = sQuery
    > cmd.Properties("xml root").Value = "root"
    > cmd.Properties("Output Stream") = xmldoc
    > cmd.Execute , , adExecuteStream + adCmdText
    > set cmd=nothing
    > cn.close
    > set cn=nothing
    > %>
    > <html>
    > ...
    > <XML id="xmlData">
    > <%=xmldoc.xml%>
    > </XML>
    > </html>
    >
    > HTH,


    I've tried running your code, Bob. It returns an error message at line 15
    and if I comment that out, another at line16
    That's these lines.
    > cmd.Properties("xml root").Value = "root"
    > cmd.Properties("Output Stream") = xmldoc


    Error message is:

    ADODB.Command (0x800A0CC1)
    Item cannot be found in the collection corresponding to the
    requested name or ordinal.
     
    Peter Morris, Oct 16, 2004
    #7
  8. Peter Morris

    Peter Morris Guest

    "Peter Morris" <nospam.ple@se> wrote in message
    news:b%Ybd.9371$...
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >
    >>
    >>
    >> I have some code somewhere that uses a Stream to get te XML. Let me look
    >> for it: ... ah! Here it is:
    >>
    >> <%@ Language=VBScript %>
    >> <%
    >> option explicit
    >> dim cn, rs,sQuery, cmd, xmldoc
    >> Const adExecuteStream = &H00000400
    >> Const adCmdText = &H0001
    >> set cn=server.CreateObject("adodb.connection")
    >> cn.open "provider=sqloledb;data source=????;" & _
    >> "user id = ???; password=????;initial catalog=northwind"
    >> Set xmldoc=server.createobject("msxml2.domdocument")
    >> Set cmd = Server.CreateObject("ADODB.Command")
    >> Set cmd.ActiveConnection = cn
    >> sQuery = "SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML auto"
    >> cmd.CommandText = sQuery
    >> cmd.Properties("xml root").Value = "root"
    >> cmd.Properties("Output Stream") = xmldoc
    >> cmd.Execute , , adExecuteStream + adCmdText
    >> set cmd=nothing
    >> cn.close
    >> set cn=nothing
    >> %>
    >> <html>
    >> ...
    >> <XML id="xmlData">
    >> <%=xmldoc.xml%>
    >> </XML>
    >> </html>
    >>
    >> HTH,

    >
    > I've tried running your code, Bob. It returns an error message at line 15
    > and if I comment that out, another at line16
    > That's these lines.
    >> cmd.Properties("xml root").Value = "root"
    >> cmd.Properties("Output Stream") = xmldoc

    >
    > Error message is:
    >
    > ADODB.Command (0x800A0CC1)
    > Item cannot be found in the collection corresponding to the
    > requested name or ordinal.


    I don't fully understand how the code works. Can you please tell
    me what's wrong?
     
    Peter Morris, Oct 16, 2004
    #8
  9. Peter Morris wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >
    >>

    >
    > I've tried running your code, Bob. It returns an error message at
    > line 15 and if I comment that out, another at line16
    > That's these lines.
    >> cmd.Properties("xml root").Value = "root"
    >> cmd.Properties("Output Stream") = xmldoc

    >
    > Error message is:
    >
    > ADODB.Command (0x800A0CC1)
    > Item cannot be found in the collection corresponding to the
    > requested name or ordinal.


    This is most likely due to a problem with the version of ADO that is running
    on your server. You should get and install the latest version of MDAC from
    the MS website.

    Bob Barrows

    --
    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], Oct 16, 2004
    #9
  10. Peter Morris wrote:
    >
    > I don't fully understand how the code works. Can you please tell
    > me what's wrong?

    I've already replied to your previous message. You waited only 3 min. before
    asking again?? I know it must seem like it sometimes, but I don't live in
    these newgroups :)
    --
    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], Oct 16, 2004
    #10
  11. Peter Morris

    Peter Morris Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Peter Morris wrote:
    >>
    >> I don't fully understand how the code works. Can you please tell
    >> me what's wrong?

    > I've already replied to your previous message. You waited only 3 min.
    > before asking again?? I know it must seem like it sometimes, but I don't
    > live in these newgroups :)


    On review, I thought my original post might appear a bit abrupt and
    rude to someone offering assistence. I thought the second post would
    make it more polite.
     
    Peter Morris, Oct 16, 2004
    #11
  12. Peter Morris

    Peter Morris Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:%23k%...
    > Peter Morris wrote:
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >>
    >>>

    >>
    >> I've tried running your code, Bob. It returns an error message at
    >> line 15 and if I comment that out, another at line16
    >> That's these lines.
    >>> cmd.Properties("xml root").Value = "root"
    >>> cmd.Properties("Output Stream") = xmldoc

    >>
    >> Error message is:
    >>
    >> ADODB.Command (0x800A0CC1)
    >> Item cannot be found in the collection corresponding to the
    >> requested name or ordinal.

    >
    > This is most likely due to a problem with the version of ADO that is
    > running on your server. You should get and install the latest version of
    > MDAC from the MS website.
    >
    > Bob Barrows


    Thank you, I'll try that.
     
    Peter Morris, Oct 16, 2004
    #12
  13. Peter Morris wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> Peter Morris wrote:
    >>>
    >>> I don't fully understand how the code works. Can you please tell
    >>> me what's wrong?

    >> I've already replied to your previous message. You waited only 3 min.
    >> before asking again?? I know it must seem like it sometimes, but I
    >> don't live in these newgroups :)

    >
    > On review, I thought my original post might appear a bit abrupt and
    > rude to someone offering assistence. I thought the second post would
    > make it more polite.


    Oh, I see. Well, that's the problem with email and newsgroup posts. No body
    language or voice inflection to help us judge the content. What seemed more
    polite to you as you wrote and sent it, seemed like a rude "what's keeping
    you?" to me. Something like this would have helped:

    Oh wait! That seemed rude and abrupt. Let me try again: ....

    Now there's no room for misinterpretation.

    I would have replied:
    There was no need to worry. It did not seem rude to me at all. In fact, we
    appreciate succinctness since it allows us to process more posts in a given
    period of time.

    Bob Barrows
    --
    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], Oct 16, 2004
    #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. sam martin

    returning xml from aspx page

    sam martin, Feb 13, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    12,527
    Tommy
    Feb 13, 2004
  2. Martin Dew
    Replies:
    2
    Views:
    359
    =?Utf-8?B?d3NNYXJrTQ==?=
    Mar 10, 2005
  3. Andy Fish
    Replies:
    6
    Views:
    464
    Hiran Chaudhuri
    Mar 5, 2004
  4. Replies:
    11
    Views:
    668
    Christos Georgiou
    May 2, 2006
  5. Erik Wasser
    Replies:
    5
    Views:
    465
    Peter J. Holzer
    Mar 5, 2006
Loading...

Share This Page