SP returning XML

P

Peter Morris

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

Bob Barrows [MVP]

Peter said:
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
 
P

Peter Morris

Bob Barrows said:
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.
 
B

Bob Barrows [MVP]

Peter said:
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
 
A

ajsmith02

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

Bob Barrows [MVP]

ajsmith02 said:
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
 
P

Peter Morris

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

Peter Morris

Peter Morris said:
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.

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

Bob Barrows [MVP]

Peter said:
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.

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
 
B

Bob Barrows [MVP]

Peter said:
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 :)
 
P

Peter Morris

Bob Barrows said:
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.
 
P

Peter Morris

Bob Barrows said:
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.
 
B

Bob Barrows [MVP]

Peter said:
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
 

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

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top