Concatenate XML without type casting to string

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 xmldeclare @XML2 xmldeclare @XML3 xmlset @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')

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]

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>