convert table data into complex xml document

I would like to convert the following two tables into the XML document below
without a bunch of UNIONS. The reason being in my case i have hundereds of
tables w/ different fields all going into a single XML document. Is there
any way to use user defined functions or something to construct just one XML
document? Or is there a way i can convert all the foos into a string, all
the bars into a string etc. then concatinate them and return them as an xml
document?
TABLE FOO
x y
________________ ____________________
1 11
2 22
TABLE BAR
a b
________________ ____________________
3 33
4 44
Using Open XML how do you convert this into:
<rootfoobar>
<foos>
<foo x='1' y='11'/>
<foo x='2' y='22'/>
</foos>
<bars>
<bar a='3' b='33'/>
<bar a='4' b='44'/>
</bars>
</rootfoobar>
The UNIONS are not so bad (the mainteance of the query is rather ugly
though).
In SQL Server 2000, you either use the mapping schemas of the mid-tier
SQLXML component or FOR XML EXPLICIT queries.
If the data has no correlation, you can also use the XML templates in SQLXML
to put individual query results together.
In SQL Server 2005, you will be able to nest FOR XML and use the new PATH
mode that avoids the UNION all. See my blog entries and links to an MSDN
whitepaper on http://sqljunkies.com/weblog/mrys
HTH
Michael
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:%23l2gkbrbEHA.3716@.TK2MSFTNGP11.phx.gbl...
>I would like to convert the following two tables into the XML document
>below
> without a bunch of UNIONS. The reason being in my case i have hundereds of
> tables w/ different fields all going into a single XML document. Is there
> any way to use user defined functions or something to construct just one
> XML
> document? Or is there a way i can convert all the foos into a string, all
> the bars into a string etc. then concatinate them and return them as an
> xml
> document?
> TABLE FOO
> x y
> ________________ ____________________
> 1 11
> 2 22
>
>
> TABLE BAR
> a b
> ________________ ____________________
> 3 33
> 4 44
>
> Using Open XML how do you convert this into:
> <rootfoobar>
> <foos>
> <foo x='1' y='11'/>
> <foo x='2' y='22'/>
> </foos>
> <bars>
> <bar a='3' b='33'/>
> <bar a='4' b='44'/>
> </bars>
> </rootfoobar>
>
>