Return different row sets using for xml?

I am returning rows using "for xml raw" from a stored procedure - works
great. But I'd like to reduce the number of interactions between my client
and the database - for example: returning two sets of different data
requires two calls to two different stored procedures.
But can one stored procedure return both data sets using "for xml"? If it
could then I can reduce the number of calls. The two data sets have
absolutely no relation to each other and bear no similarities in structure.
E.g.
To get data from the person table and the hotel table currently requires two
stored procedure calls to GetPeople and GetHotels. Can it be combined to
GetPeopleAndHotels?
<data>
<people>
<person name = "fred"/>
<person name = "bob"/>
</people>
<hotels>
<hotel country = "uk"/>
<hotel country = "us"/>
</hotel>
</data>You can call more than one FOR XML query inside a stored proc.
Also, if you need more semantic markup than what the RAW mode can give you,
you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am returning rows using "for xml raw" from a stored procedure - works
> great. But I'd like to reduce the number of interactions between my client
> and the database - for example: returning two sets of different data
> requires two calls to two different stored procedures.
> But can one stored procedure return both data sets using "for xml"? If it
> could then I can reduce the number of calls. The two data sets have
> absolutely no relation to each other and bear no similarities in
> structure.
> E.g.
> To get data from the person table and the hotel table currently requires
> two
> stored procedure calls to GetPeople and GetHotels. Can it be combined to
> GetPeopleAndHotels?
> <data>
> <people>
> <person name = "fred"/>
> <person name = "bob"/>
> </people>
> <hotels>
> <hotel country = "uk"/>
> <hotel country = "us"/>
> </hotel>
> </data>
>|||Do you know if returning multiple xml result sets would be compatible with
the BizTalk 2004 SQL Adapter?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> You can call more than one FOR XML query inside a stored proc.
> Also, if you need more semantic markup than what the RAW mode can give
you,
> you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
client
it
>
>|||Sorry, but I don't know. I would assume that you expose the result through
the stream interface and add the root element through the stream property,
it should look like a single XML document.
You may want to ask somebody over in the BTS newsgroup...
Best regards
Michael
"Xerox" <info@.thinkscape.com> wrote in message
news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you know if returning multiple xml result sets would be compatible with
> the BizTalk 2004 SQL Adapter?
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> you,
> client
> it
>|||Thanks for the help. I tried it out and it does work fine! The two "for xml"
queries return the data to BizTalk as a single XML document.
The only trouble is, and I wonder if you can help me, is that I would like
both queries to return their xml data under a parent tag: ie. return all
<Customer/> tags under <Customers/>.
This is my procedure:
create procedure GetXML as
select 1 as Tag, Null as Parent, EmployeeID as 'Employee!1!EmployeeID' from
employees for xml explicit;
select 1 as Tag, Null as Parent, CustomerID as 'Customer!1!CustomerID' from
customers for xml explicit;
go
which returns xml data like this:
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
<Customer CustomerID="SPLIR" />
But I would like it to return data like this:
<Employees>
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
</Employees>
<Customers>
<Customer CustomerID="SPLIR" />
</Customers>
Do you know if it is possible? Thank you!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e7xLiVX6EHA.2584@.TK2MSFTNGP10.phx.gbl...
> Sorry, but I don't know. I would assume that you expose the result through
> the stream interface and add the root element through the stream property,
> it should look like a single XML document.
> You may want to ask somebody over in the BTS newsgroup...
> Best regards
> Michael
> "Xerox" <info@.thinkscape.com> wrote in message
> news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
with
mode.
works
If
>