Martin Giessen Martin Giessen - 2 months ago 7
SQL Question

SQL Server FOR XML: Joining XML nodes into one XML

I have a table like the following:

DECLARE @myTable TABLE (Ordinal INT, MyXML XML)
INSERT INTO @myTable (Ordinal, MyXML) VALUES (1, '<Item AnyAttribute="anyValue">1</Item>')
INSERT INTO @myTable (Ordinal, MyXML) VALUES (1, '<Item AnyAttribute="anyValue">2</Item>')


When I join the XML fragments into one big XML with:

SELECT MyXML AS 'OmitMe' FROM @myTable FOR XML PATH(''), ROOT('Items'), TYPE


I get:

<Items>
<OmitMe>
<Item AnyAttribute="anyValue">1</Item>
</OmitMe>
<OmitMe>
<Item AnyAttribute="anyValue">2</Item>
</OmitMe>
</Items>


But I would like to get:

<Items>
<Item AnyAttribute="anyValue">1</Item>
<Item AnyAttribute="anyValue">2</Item>
</Items>


The use variables are not an option because that would imply the necessaty of a looping mechinisme like a cursor.

Any help will be appreciated!

Martin

EDIT:

A solution provided by Ed Harper can be incorporated into the larger scheme of things:

SELECT CAST(REPLACE(CAST((SELECT MyXML AS Items FROM @myTable FOR XML PATH ('')) AS NVARCHAR(MAX)),N'</Items><Items>',N'') AS XML)


It's sub-optimal, but it does work.

Answer

Have a look at Columns with a Name Specified as a Wildcard Character

If the column name specified is a wildcard character (*), the content of that column is inserted as if there is no column name specified.

SELECT MyXML AS '*' FROM @myTable FOR XML PATH(''), ROOT('Items'), TYPE
Comments