CODEWITHSUNDEEP
sql-serverxmlnestedxmlroot
Rizwan Shaikh
Rizwan Shaikh

Reputation: 95

Nested FOR XML results with SQL Server

My MS SQL table has following data

ID    CONTENT     UDH         TO       FROM
-------------------------------------------
1     HELLO       1          9812      22
2     HELLO       1          9813      22
3     HELLO       1          9814      22

and i want to select values from this table in below format without any root node.

<SMS CONTENT="HELLO" UDH="1">
<ADDRESS TO="9812" FROM="22"/>
</SMS>

<SMS CONTENT="HELLO" UDH="1">
<ADDRESS TO="9813" FROM="22"/>
</SMS>

<SMS CONTENT="HELLO" UDH="1">
<ADDRESS TO="9814" FROM="22"/>
</SMS>

it is a clear version of what i posted earlier here

Upvotes: 1

Views: 38

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Try it like this (according your comment):

    DECLARE @tbl TABLE(ID INT,CONTENT VARCHAR(100),UDH INT,[TO] INT,[FROM] INT);
INSERT INTO @tbl VALUES
 (1,'HELLO',1,9812,22)
,(2,'HELLO',1,9813,22)
,(3,'HELLO',1,9814,22);

SELECT tbl.CONTENT AS [@CONTENT]
      ,tbl.UDH AS [@UDH]
      ,tbl.[TO] AS [ADDRESS/@TO]
      ,tbl.[FROM] AS [ADDRESS/@FROM]
FROM @tbl AS tbl
FOR XML PATH('SMS')

/* Result
<SMS CONTENT="HELLO" UDH="1">
  <ADDRESS TO="9812" FROM="22" />
</SMS>
<SMS CONTENT="HELLO" UDH="1">
  <ADDRESS TO="9813" FROM="22" />
</SMS>
<SMS CONTENT="HELLO" UDH="1">
  <ADDRESS TO="9814" FROM="22" />
</SMS>
*/

Upvotes: 2

Related Questions