ExecuteXmlReader w/ Multiple result sets from SP

[Mark Miller]

I have an sp that outputs multiple xml fragments w/ no root. The sp calls
individual sp's to output the correct set of data and each "type" has
different fields.

ex.:
<LEADERBOARD @Type='Rushing'>
<LEADER @Carries=''/>
</LEADERBOARD>
<LEADERBOARD @Type='QBRating'>
<LEADER @Rating='' @CompletionPct=''/>
</LEADERBOARD>
<LEADERBOARD @Type='Tackles'>
<LEADER @Assisted='' @Tackles='' @Total=''/>
</LEADERBOARD>

I need to take the output and use XSLT to translate it to custom output. But
no matter what I try I hit a dead end. Following is what I have tried.
1)
CREATE PROCEDURE GetAllLeaders_XML
@Param1, @Param2, @Param3
AS

SET NOCOUNT ON

SELECT '<LEADERBOARDS>'
EXEC Leaderboard_XML @LeaderType1, @param1, @param2, @param3
EXEC Leaderboard_XML @LeaderType2, @param1, @param2, @param3
EXEC Leaderboard_XML @LeaderType3, @param1, @param2, @param3
SELECT '</LEADERBOARDS>'

GO

with the following code (C#):
XmlReader rdrXml = cmdXML.ExecuteXmlReader();

or this:

DataSet dsXML = new DataSet();

dsXML.ReadXml(cmdXML.ExecuteXmlReader(), XmlReadMode.Fragment);

result: "Invalid command sent to ExecuteXmlReader(). The command must return
an XML result.
2) If I remove the "root" LEADERBOARDS from before and after the sp's:

with the same code (C#)
(see #1 above)

result: "Object reference not set to an instance of an object. "

(btw. It doesn't matter if I use "ROOT" or "LEADERBOARDS" as the root
element).

I have looked at SqlXmlCommand but I don't see how I can add multiple
parameters to pass to the sp. So if anyone can point me in the right
direction or provide me with some sample code to that effect I'd appreciate
any help. Even if only to let me know if I could get a better answer from
another ng. Everything I have tried works fine w/ other sp's that return a
single, well-formed frament w/ a root element. I need help ASAP,
PLEEEEAASSSEEEEEE!

[Drebin]

Inside those stored procedures - you are doing "for xml" on the select
statements??
"Mark Miller" <no**********@waveshift.com> wrote in message
news:eP**************@TK2MSFTNGP10.phx.gbl...

I have an sp that outputs multiple xml fragments w/ no root. The sp calls
individual sp's to output the correct set of data and each "type" has
different fields.

ex.:
<LEADERBOARD @Type='Rushing'>
<LEADER @Carries=''/>
</LEADERBOARD>
<LEADERBOARD @Type='QBRating'>
<LEADER @Rating='' @CompletionPct=''/>
</LEADERBOARD>
<LEADERBOARD @Type='Tackles'>
<LEADER @Assisted='' @Tackles='' @Total=''/>
</LEADERBOARD>

I need to take the output and use XSLT to translate it to custom output. But no matter what I try I hit a dead end. Following is what I have tried.
1)
CREATE PROCEDURE GetAllLeaders_XML
@Param1, @Param2, @Param3
AS

SET NOCOUNT ON

SELECT '<LEADERBOARDS>'
EXEC Leaderboard_XML @LeaderType1, @param1, @param2, @param3
EXEC Leaderboard_XML @LeaderType2, @param1, @param2, @param3
EXEC Leaderboard_XML @LeaderType3, @param1, @param2, @param3
SELECT '</LEADERBOARDS>'

GO

with the following code (C#):
XmlReader rdrXml = cmdXML.ExecuteXmlReader();

or this:

DataSet dsXML = new DataSet();

dsXML.ReadXml(cmdXML.ExecuteXmlReader(), XmlReadMode.Fragment);

result: "Invalid command sent to ExecuteXmlReader(). The command must return an XML result.
2) If I remove the "root" LEADERBOARDS from before and after the sp's:

with the same code (C#)
(see #1 above)

result: "Object reference not set to an instance of an object. "

(btw. It doesn't matter if I use "ROOT" or "LEADERBOARDS" as the root
element).

I have looked at SqlXmlCommand but I don't see how I can add multiple
parameters to pass to the sp. So if anyone can point me in the right
direction or provide me with some sample code to that effect I'd appreciate any help. Even if only to let me know if I could get a better answer from
another ng. Everything I have tried works fine w/ other sp's that return a
single, well-formed frament w/ a root element. I need help ASAP,
PLEEEEAASSSEEEEEE!

[Mark Miller]

If you are referring to the statments:
SELECT '<LEADERBOARDS>'
and
SELECT '</LEADERBOARDS>'

no, I am not. I was not aware that was possible. Won't that wrap the text
"<LEADERBOARDS>" in another XML tag? If this is what you are referring to
could you please provide a sample of how I would do that?

But if you are referring to the statements
EXEC Leaderboard_XML @LeaderType1, @param1, @param2, @param3
EXEC Leaderboard_XML @LeaderType2, @param1, @param2, @param3
EXEC Leaderboard_XML @LeaderType3, @param1, @param2, @param3

Yes, I am. I am using FOR XML EXPLICIT

"Drebin" <tR*************@hotmail.com> wrote in message
news:ur*****************@newssvr33.news.prodigy.co m...

Inside those stored procedures - you are doing "for xml" on the select
statements??
"Mark Miller" <no**********@waveshift.com> wrote in message
news:eP**************@TK2MSFTNGP10.phx.gbl...

I have an sp that outputs multiple xml fragments w/ no root. The sp calls individual sp's to output the correct set of data and each "type" has
different fields.

ex.:
<LEADERBOARD @Type='Rushing'>
<LEADER @Carries=''/>
</LEADERBOARD>
<LEADERBOARD @Type='QBRating'>
<LEADER @Rating='' @CompletionPct=''/>
</LEADERBOARD>
<LEADERBOARD @Type='Tackles'>
<LEADER @Assisted='' @Tackles='' @Total=''/>
</LEADERBOARD>

I need to take the output and use XSLT to translate it to custom output.

But

no matter what I try I hit a dead end. Following is what I have tried.
1)
CREATE PROCEDURE GetAllLeaders_XML
@Param1, @Param2, @Param3
AS

SET NOCOUNT ON

SELECT '<LEADERBOARDS>'
EXEC Leaderboard_XML @LeaderType1, @param1, @param2, @param3
EXEC Leaderboard_XML @LeaderType2, @param1, @param2, @param3
EXEC Leaderboard_XML @LeaderType3, @param1, @param2, @param3
SELECT '</LEADERBOARDS>'

GO

with the following code (C#):
XmlReader rdrXml = cmdXML.ExecuteXmlReader();

or this:

DataSet dsXML = new DataSet();

dsXML.ReadXml(cmdXML.ExecuteXmlReader(), XmlReadMode.Fragment);

result: "Invalid command sent to ExecuteXmlReader(). The command must

return

an XML result.
2) If I remove the "root" LEADERBOARDS from before and after the sp's:

with the same code (C#)
(see #1 above)

result: "Object reference not set to an instance of an object. "

(btw. It doesn't matter if I use "ROOT" or "LEADERBOARDS" as the root
element).

I have looked at SqlXmlCommand but I don't see how I can add multiple
parameters to pass to the sp. So if anyone can point me in the right
direction or provide me with some sample code to that effect I'd

appreciate

any help. Even if only to let me know if I could get a better answer from another ng. Everything I have tried works fine w/ other sp's that return a single, well-formed frament w/ a root element. I need help ASAP,
PLEEEEAASSSEEEEEE!

[Mark Miller]

I appreciate your help. However, my problem actually lies with getting the
result set to the client. The root node is the least of my worries. I only
added it hoping the ExecuteXmlReader method would then interpret the
resulting stream as a well formed XML document as opposed to a bunch of
fragments which is what I think it is doing.

I'm going to look into using a standard SqlDataReader or DataSet object to
get the data and then glue it all together before trying to pass it off as
all one XML docuement. But please if anyone has a better solution or can
point me in the right direction to using the SqlXmlCommand object I would
appreciate it.

Thanks,
mark
"Drebin" <tR*************@hotmail.com> wrote in message
news:N2*****************@newssvr32.news.prodigy.co m...

Yeah, you are right, you can only do individual tags, like this:

SELECT TOP 1
1 as Tag,
NULL as Parent,
GetDate() as [Leaderboard!1!]
FOR XML EXPLICIT

But not put the entire bit within that container - which is what I was
thinking.. My bad.

On another note, when I've done this in ASP, I always put the outside
container (like Leaderboard) on the client-side, then inserted the rest of
the XML inside that node.. but I should note, I have yet to get a .NET
version of this concept working, so I'm not much of an expert... :o)
"Mark Miller" <no**********@waveshift.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

If you are referring to the statments:
SELECT '<LEADERBOARDS>'
and
SELECT '</LEADERBOARDS>'

no, I am not. I was not aware that was possible. Won't that wrap the text

"<LEADERBOARDS>" in another XML tag? If this is what you are referring to could you please provide a sample of how I would do that?

But if you are referring to the statements
EXEC Leaderboard_XML @LeaderType1, @param1, @param2, @param3
EXEC Leaderboard_XML @LeaderType2, @param1, @param2, @param3
EXEC Leaderboard_XML @LeaderType3, @param1, @param2, @param3

Yes, I am. I am using FOR XML EXPLICIT

"Drebin" <tR*************@hotmail.com> wrote in message
news:ur*****************@newssvr33.news.prodigy.co m...

Inside those stored procedures - you are doing "for xml" on the select
statements??
"Mark Miller" <no**********@waveshift.com> wrote in message
news:eP**************@TK2MSFTNGP10.phx.gbl...
> I have an sp that outputs multiple xml fragments w/ no root. The sp

calls

> individual sp's to output the correct set of data and each "type" has > different fields.
>
> ex.:
> <LEADERBOARD @Type='Rushing'>
> <LEADER @Carries=''/>
> </LEADERBOARD>
> <LEADERBOARD @Type='QBRating'>
> <LEADER @Rating='' @CompletionPct=''/>
> </LEADERBOARD>
> <LEADERBOARD @Type='Tackles'>
> <LEADER @Assisted='' @Tackles='' @Total=''/>
> </LEADERBOARD>
>
> I need to take the output and use XSLT to translate it to custom

output. But
> no matter what I try I hit a dead end. Following is what I have tried. > 1)
> CREATE PROCEDURE GetAllLeaders_XML
> @Param1, @Param2, @Param3
> AS
>
> SET NOCOUNT ON
>
> SELECT '<LEADERBOARDS>'
> EXEC Leaderboard_XML @LeaderType1, @param1, @param2, @param3
> EXEC Leaderboard_XML @LeaderType2, @param1, @param2, @param3
> EXEC Leaderboard_XML @LeaderType3, @param1, @param2, @param3
> SELECT '</LEADERBOARDS>'
>
> GO
>
> with the following code (C#):
> XmlReader rdrXml = cmdXML.ExecuteXmlReader();
>
> or this:
>
> DataSet dsXML = new DataSet();
>
> dsXML.ReadXml(cmdXML.ExecuteXmlReader(), XmlReadMode.Fragment);
>
>
>
> result: "Invalid command sent to ExecuteXmlReader(). The command must return
> an XML result.
>
>
> 2) If I remove the "root" LEADERBOARDS from before and after the sp's: >
> with the same code (C#)
> (see #1 above)
>
> result: "Object reference not set to an instance of an object. "
>
> (btw. It doesn't matter if I use "ROOT" or "LEADERBOARDS" as the root > element).
>
> I have looked at SqlXmlCommand but I don't see how I can add multiple > parameters to pass to the sp. So if anyone can point me in the right
> direction or provide me with some sample code to that effect I'd
appreciate
> any help. Even if only to let me know if I could get a better answer

from

> another ng. Everything I have tried works fine w/ other sp's that

return

a

> single, well-formed frament w/ a root element. I need help ASAP,
> PLEEEEAASSSEEEEEE!
>
>