Retrieving and Combining XML

I have a table in a SQL Server 2000 db that contains xml in one column.
I'd like to retrieve the xml from several records (10,000 actually) and
wrap them up into one xml document like this:
content of xmlData column:
<foo>
..
</foo>
Desired output:
<bar>
<foo>
..
</foo>
<foo>
..
</foo>
<foo>
..
</foo>
</bar>
I tried using FOR XML EXPLICIT, but that parsed all my tags to < and
>. How can I preserve the stored xml and output a single xml file
containing the stored xml for multiple records?
thanks
-ivan.FOR XML EXPLICIT was a good try. But you will need the !xml directive in
your column alias.
Eg,
select ... , xmlData as "element!1!row!xml" ... FOR XML EXPLICIT.
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns96FBA3DBC554BnewsNOSPAMgilly3com
@.207.46.248.16...
>I have a table in a SQL Server 2000 db that contains xml in one column.
> I'd like to retrieve the xml from several records (10,000 actually) and
> wrap them up into one xml document like this:
> content of xmlData column:
> <foo>
> ...
> </foo>
> Desired output:
> <bar>
> <foo>
> ...
> </foo>
> <foo>
> ...
> </foo>
> <foo>
> ...
> </foo>
> </bar>
> I tried using FOR XML EXPLICIT, but that parsed all my tags to < and
> >. How can I preserve the stored xml and output a single xml file
> containing the stored xml for multiple records?
> thanks
> -ivan.|||"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in
news:uZgb5Zq2FHA.3880@.TK2MSFTNGP12.phx.gbl:

> FOR XML EXPLICIT was a good try. But you will need the !xml directive
in
> your column alias.
> Eg,
> select ... , xmlData as "element!1!row!xml" ... FOR XML EXPLICIT.
> Best regards
> Michael
Thanks, that fixes my formatting problem, but I still had trouble
getting each record under a common root node.
My sql looked like this:
select
1 tag,
null parent,
[xmlData] [xRoot!1!xElement!xml]
from xmlTable
for xml explicit
this gave each record two parent nodes like this with no common root
node:
<xRoot>
<xElement>
<foo>
..
</foo>
</xElement>
</xRoot>
<xRoot>
<xElement>
<foo>
..
</foo>
</xElement>
</xRoot>
I want one parent node, and for that node to be the root of all the
records. I managed to make it work by adding a parent node in my
select, and eliminating extra nodes by using !xmltext, instead of !xml
like this:
select
1 tag,
null parent,
null [xRoot!1!!xmltext],
null [foo!2!!xmltext]
union all
select 2,
1,
null,
[xmlData]
from xmlTable
for xml explicit
This works, but it seems like a bit of a hack. Is there a more elegant
solution? If not, I'll just be happy this works as well as it does.
thanks
-ivan.|||In SQL Server 2005, you can use ROOT('myRoot') in the FOR XML clause.
In SQL Server 2000, your workaround works. Alternatively, there is a root
property on your connection that you can set in ADO, OLEDB, ADO.Net to get
the root element added on the client.
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns96FCA5F95596AnewsNOSPAMgilly3com
@.207.46.248.16...
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in
> news:uZgb5Zq2FHA.3880@.TK2MSFTNGP12.phx.gbl:
>
> in
> Thanks, that fixes my formatting problem, but I still had trouble
> getting each record under a common root node.
> My sql looked like this:
> select
> 1 tag,
> null parent,
> [xmlData] [xRoot!1!xElement!xml]
> from xmlTable
> for xml explicit
> this gave each record two parent nodes like this with no common root
> node:
> <xRoot>
> <xElement>
> <foo>
> ...
> </foo>
> </xElement>
> </xRoot>
> <xRoot>
> <xElement>
> <foo>
> ...
> </foo>
> </xElement>
> </xRoot>
> I want one parent node, and for that node to be the root of all the
> records. I managed to make it work by adding a parent node in my
> select, and eliminating extra nodes by using !xmltext, instead of !xml
> like this:
>
> select
> 1 tag,
> null parent,
> null [xRoot!1!!xmltext],
> null [foo!2!!xmltext]
> union all
> select 2,
> 1,
> null,
> [xmlData]
> from xmlTable
> for xml explicit
> This works, but it seems like a bit of a hack. Is there a more elegant
> solution? If not, I'll just be happy this works as well as it does.
> thanks
> -ivan.