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.

Answer 1

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('')