Concatenate XML without type casting to string

Question

I have the following XML generated from various tables in my SQL SERVER database

<XMLData>
...
<Type>1</Type>
...
</XMLData>

AND

<XMLData>
...
<Type>2</Type>
...
</XMLData>

AND

<XMLData>
...
<Type>3</Type>
...
</XMLData>

The final output I need is single combined as follows:

<AllMyData>
    <XMLData>
        ...
        <Type>1</Type>
        ...
    </XMLData>
    <XMLData>
        ...
        <Type>2</Type>
        ...
    </XMLData>
    <XMLData>
        ...
        <Type>3</Type>
        ...
    </XMLData>
<AllMyData>

NOTE - all the independent elements that I am combining have the same tag name.

Thanks in advance for looking this up.

Answer 1

I have the following XML generated from various tables in my SQL SERVER database

Depends on how you have it but if it is in a XML variable you can do like this.

declare @XML1 xml
declare @XML2 xml
declare @XML3 xml

set @XML1 = '<XMLData><Type>1</Type></XMLData>'
set @XML2 = '<XMLData><Type>2</Type></XMLData>'
set @XML3 = '<XMLData><Type>3</Type></XMLData>'

select @XML1, @XML2, @XML3 
for xml path('AllMyData')

Answer 2

I can't comment but can answer so even though I think a comment is more appropriate, I'll expand on what rainabba answered above to add a bit more control. My .Net code needs to know the column name returned so I can't rely on auto-generated names but needed the very tip rainabba provided above otherwise.

This way, the xml can effectively be concatenated into a single row and the resulting column named. You could use this same approach to assign the results to an XML variable and return that from a PROC also.

SELECT (
 SELECT XmlData as [*]
 FROM
     (
     SELECT
         xmlResult AS [*]
     FROM
         @XmlRes
     WHERE
         xmlResult IS NOT NULL
     FOR XML PATH(''), TYPE
     ) as DATA(XmlData)
 FOR XML PATH('')
) as [someColumnName]

Answer 3

If you use for xml type, you can combine the XML columns without casting them. For example:

select  *
from    (
        select  (
                select  1 as Type
                for xml path(''), type
                )
        union all
        select  (
                select  2 as Type
                for xml path(''), type
                )
        union all
        select  (
                select  3 as Type
                for xml path(''), type
                )
        ) as Data(XmlData)
for xml path(''), root('AllMyData'), type

This prints:

<AllMyData>
    <XmlData>
        <Type>1</Type>
    </XmlData>
    <XmlData>
        <Type>2</Type>
    </XmlData>
    <XmlData>
        <Type>3</Type>
    </XmlData>
</AllMyData>