Re: Return different row sets using for xml?

• Previous message: Michael Rys [MSFT]: "Re: How do I select only the text of certain elements?"
• In reply to: Michael Rys [MSFT]: "Re: Return different row sets using for xml?"
• Messages sorted by: [ date ] [ thread ]

Date: Fri, 24 Dec 2004 11:50:21 -0000

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

• Previous message: Michael Rys [MSFT]: "Re: How do I select only the text of certain elements?"
• In reply to: Michael Rys [MSFT]: "Re: Return different row sets using for xml?"
• Messages sorted by: [ date ] [ thread ]