MS SQL XML - Retrieving and Combining XML

Author

Retrieving and Combining XML

gilly3

2005-10-27, 9:26 am

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]

2005-10-27, 9:26 am

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:Xns96FBA3DBC554
BnewsNOSPAMgilly3com
@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.

gilly3

2005-10-27, 8:23 pm

"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] & #91;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 & #91;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.

Michael Rys [MSFT]

2005-10-29, 8:23 pm

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:Xns96FCA5F95596
AnewsNOSPAMgilly3com
@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] & #91;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 & #91;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.

Sponsored Links