Combine two lots of xml in to one?

I have two stored procedures each returning xml using for xml explicit:
GetOrders returns data as:
<Orders><Order id = "1"/><Order id = "2"></Orders>
GetCustomers returns data as:
<Customers><Customer id = "1"/><Customer id = "2"/></Customers>
Now what I want is a third procedure that reuses both these stored
procedures to get customers and orders like so:
GetCustomersAndOrders returns data as:
<CustomersAndOrders>
<Orders>
<Order id = "1"/>
<Order id = "2">
</Orders>
<Customers>
<Customer id = "1"/>
<Customer id = "2"/>
</Customers>
</CustomersAndOrders>
Is there a way to do it?
Thanks!In SQL Server 2000, you have to do this on the mid-tier. You can use the
SQLXML templates for example.
In SQL Server 2005, you would need to change the stored procs into
user-defined functions and use another FOR XML call to compose them, if you
want to do it on the server.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I have two stored procedures each returning xml using for xml explicit:
> GetOrders returns data as:
> <Orders><Order id = "1"/><Order id = "2"></Orders>
> GetCustomers returns data as:
> <Customers><Customer id = "1"/><Customer id = "2"/></Customers>
> Now what I want is a third procedure that reuses both these stored
> procedures to get customers and orders like so:
> GetCustomersAndOrders returns data as:
> <CustomersAndOrders>
> <Orders>
> <Order id = "1"/>
> <Order id = "2">
> </Orders>
> <Customers>
> <Customer id = "1"/>
> <Customer id = "2"/>
> </Customers>
> </CustomersAndOrders>
> Is there a way to do it?
> Thanks!
>|||Thanks for your feedback. Shame that it is not possible though.
Is there a way to do it, say, by casting both lots of xml data to strings
and concatenating them with surrounding root tags?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2000, you have to do this on the mid-tier. You can use the
> SQLXML templates for example.
> In SQL Server 2005, you would need to change the stored procs into
> user-defined functions and use another FOR XML call to compose them, if
you
> want to do it on the server.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>|||You cannot cast results of FOR XML in SQL Server 2000 since it can only be
transported to the mid-tier. And even in SQL Server 2005, you cannot cast
the result of a stored procedure since stored procedures operate via a
side-effect.
This is not only the case for XML but for any result that a stored proc
produces as a side-effect.
So the best way to do what you want is on the client-side.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:%23eG%23roJ9EHA.2608@.TK2MSFTNGP10.phx.gbl...
> Thanks for your feedback. Shame that it is not possible though.
> Is there a way to do it, say, by casting both lots of xml data to strings
> and concatenating them with surrounding root tags?
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> you
>