CODEWITHSUNDEEP
sqlsql-serverxml
Martin Giessen
Martin Giessen

Reputation: 147

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.

Upvotes: 2

Views: 72

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 2

Related Questions