CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
disasterkid
disasterkid

Reputation: 7278

Surround all rows in a root Xml node (eliminating the column name node)

I have a table with only one column of type Xml and also named Xml. Each row represents a <recipient> item.

<receipt>
  <memberid>55555555</memberid>
  <purchaseid>4408888888859447</purchaseid>
  <time>2017-02-01</time>
  <depID>50277</depID>
  <amount>1398</amount>
  <theme>Herr</theme>
  <parentgroup></parentgroup>
</receipt>

What I want to do is surround all these items in one parent node of <recipients> so they will look something like this

<recipients>
    <receipt>
      <memberid>55555555</memberid>
      <purchaseid>4408888888859447</purchaseid>
      <time>2017-02-01</time>
      <depID>50277</depID>
      <amount>1398</amount>
      <theme>Herr</theme>
      <parentgroup></parentgroup>
    </receipt>
    <receipt>
      <memberid>55555555</memberid>
      <purchaseid>4408888888859447</purchaseid>
      <time>2017-02-01</time>
      <depID>50277</depID>
      <amount>1398</amount>
      <theme>Herr</theme>
      <parentgroup></parentgroup>
    </receipt>
    <receipt>
      <memberid>55555555</memberid>
      <purchaseid>4408888888859447</purchaseid>
      <time>2017-02-01</time>
      <depID>50277</depID>
      <amount>1398</amount>
      <theme>Herr</theme>
      <parentgroup></parentgroup>
    </receipt>
</recipients>

When I run the query like:

SELECT [xml] 
  FROM MyTable
FOR XML PATH('recipients')

I get an extra <xml> node for every record and this is what I am trying to get rid of.

Upvotes: 1

Views: 62

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

Declare @YourTable table (ID int,XML xml)
Insert Into @YourTable values
(1,'<receipt><memberid>55555555</memberid><purchaseid>4408888888859447</purchaseid><time>2017-02-01</time><depID>50277</depID> <amount>1398</amount><theme>Herr</theme><parentgroup></parentgroup></receipt>'),
(2,'<receipt><memberid>66666666</memberid><purchaseid>9999999999999999</purchaseid><time>2017-02-01</time><depID>50277</depID> <amount>1398</amount><theme>Herr</theme><parentgroup></parentgroup></receipt>')

Select XML AS [*]
 From  @YourTable
 For XML Path(''),Root('recipients')

Returns

<recipients>
  <receipt>
    <memberid>55555555</memberid>
    <purchaseid>4408888888859447</purchaseid>
    <time>2017-02-01</time>
    <depID>50277</depID>
    <amount>1398</amount>
    <theme>Herr</theme>
    <parentgroup />
  </receipt>
  <receipt>
    <memberid>66666666</memberid>
    <purchaseid>9999999999999999</purchaseid>
    <time>2017-02-01</time>
    <depID>50277</depID>
    <amount>1398</amount>
    <theme>Herr</theme>
    <parentgroup />
  </receipt>
</recipients>

EDIT:

Updated Answer due to Shnugo's background info. As always, very much appreciated.

Upvotes: 2

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

this is no new answer, just some background.

This "trick" works, because the resulting column is nameless.

Look at this:

Declare @Dummy table (SomeValue INT)
Insert Into @Dummy values(1);

--Query                                --Column's Caption
----------------------------------------------------------
SELECT SomeValue FROM @Dummy;          --SomeValue
SELECT SomeValue+1-1 FROM @Dummy       --nameless due to the calculation
SELECT ISNULL(SomeValue,0) FROM @Dummy --nameless due to the function's usage

Any kind of calculation or usage of a function will result in a nameless column. Try to use SELECT ... INTO ... FROM or WITH CTE AS (SELECT ...) with a not named column and you will get an exception.

In conncetion with FOR XML PATH it can be very important to know this. If there is a column name, the XML engine will use it as element's name.

Try this (thx John for the sample code)

Declare @YourTable table (ID int,XML xml)
Insert Into @YourTable values
(1,'<receipt><memberid>55555555</memberid><purchaseid>4408888888859447</purchaseid><time>2017-02-01</time><depID>50277</depID> <amount>1398</amount><theme>Herr</theme><parentgroup></parentgroup></receipt>'),
(2,'<receipt><memberid>66666666</memberid><purchaseid>9999999999999999</purchaseid><time>2017-02-01</time><depID>50277</depID> <amount>1398</amount><theme>Herr</theme><parentgroup></parentgroup></receipt>')

John's answer works due to the usage of a function

Select cast(XML as xml) 
 From  @YourTable
 For XML Path(''),Root('recipients')

Using AS [*] as alias works too 

Select XML AS [*]
 From  @YourTable
 For XML Path(''),Root('recipients')

The best way - formally spoken - is this 

Select XML AS [node()] 
 From  @YourTable
 For XML Path(''),Root('recipients')

Any silly function or calculation has this effect

Select ISNULL(XML,XML)
 From  @YourTable
 For XML Path(''),Root('recipients')

Whenever people use FOR XML PATH as concatenation work around, they use this without knowing. The typical SELECT ', ' + SomeColumn leads to an unnamed result. Without the ', ' there would occur a "strange" element...

Upvotes: 2

Related Questions