Convert rows into XML nodes in SQL

Question

I have to return XML from the two column table like the below format

TABLE

month       count
-----       ----
January     578
February    300
March       147
April       45
May         8

XML

<January>578</January>
<February>300</February>
<March>147</March>
<April>45</April>
<May>8</May>

I have tried the below SQL statement,

SELECT * FROM #temp;

SELECT (
   SELECT monthId AS 'month/@atr', count AS month
   FROM #temp
   FOR XML PATH(''), TYPE
) 
FOR XML PATH('')

And I know the above script is for getting the first column value as attribute. In my case, I need first column value as node and second one as its value.

Thanks for help.

Is it possible by SQL query itself?

Justin
– Justin

2016-07-25 10:15:48 +00:00
Commented Jul 25, 2016 at 10:15 — Justin
– Justin, Commented Jul 25, 2016 at 10:15

Devart · Accepted Answer · 2016-07-25 10:20:50Z

4

DECLARE @t TABLE ([month] VARCHAR(50) PRIMARY KEY, [count] INT)

INSERT INTO @t
VALUES
    ('January', 578), ('February', 300),
    ('March', 147), ('April', 45), ('May', 8)

SELECT *
FROM @t
PIVOT (
    SUM(count)
    FOR month IN ([January], [February], [March], [April], [May], [June], [Jule]) 
) p
FOR XML PATH('')

answered Jul 25, 2016 at 10:20

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gottfried Lesigang Over a year ago

As the month's names are a closed set, PIVOT is a good approach... +1 from my side.

Jatin Patel · Accepted Answer · 2016-07-25 10:35:04Z

4

its odd, but serve the purpose,

DECLARE @MyTable TABLE ( Month VARCHAR(20), Count INT)

INSERT INTO @MyTable (Month, Count) 
VALUES 
     ('January' , 578)
    ,('February', 300)
    ,('March'   , 147)
    ,('April'   , 45 )
    ,('May'     , 8  )

SELECT 
  CAST('<' + Month + '>' + CAST(Count AS VARCHAR(20)) + '</' + Month + '>' AS XML) 
FROM @MyTable
FOR XML PATH('')

---- Output ----

<January>578</January>
<February>300</February>
<March>147</March>
<April>45</April>
<May>8</May>

answered Jul 25, 2016 at 10:35

Jatin Patel

2,11413 silver badges13 bronze badges

1 Comment

Gottfried Lesigang Over a year ago

I would (almost) never advise to build XML with string methods, but in this case it is a very creative approach, +1 from my side!

Gottfried Lesigang · Accepted Answer · 2016-07-25 10:44:43Z

On a direct way you can (maybe) use pivot or you use dynamic SQL:

DECLARE @tbl TABLE([month] VARCHAR(100),[count] INT);
INSERT INTO @tbl VALUES
 ('January',578)
,('February',300)
,('March',147)
,('April',45)
,('May',8);

DECLARE @cmd VARCHAR(MAX)=
'SELECT ' +
(
    STUFF(
    (
        SELECT ',' + CAST(tbl.[count] AS VARCHAR(100)) + ' AS [' + tbl.[month] + ']'
        FROM @tbl AS tbl
        FOR XML PATH('')
    ),1,1,''
    ) 
)
+
' FOR XML PATH('''');';

EXEC(@cmd);

The result

<January>578</January>
<February>300</February>
<March>147</March>
<April>45</April>
<May>8</May>

But I would not do this...

It was much better (much easier to query!) to create a structure like this:

SELECT tbl.[month] AS [@name]
      ,tbl.[count] AS [*]
FROM @tbl AS tbl
FOR XML PATH('month');

The result

<month name="January">578</month>
<month name="February">300</month>
<month name="March">147</month>
<month name="April">45</month>
<month name="May">8</month>

Collectives™ on Stack Overflow

Convert rows into XML nodes in SQL

3 Answers 3

1 Comment

1 Comment

But I would not do this...

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

But I would not do this...

Comments

Your Answer

Sign up or log in

Post as a guest

Related