SQL Server table to xml

Question

Hello, this time i have question how to convert MSSQL table to XML

My source SQL table:

+-----------+-----------------+
|atributname|atributvalue     |
+-----------+-----------------+
|phone      |222              |
|param4     |bbbbcdsfceecc    |
|param3     |bbbbcdsfceecc    |
|param2     |bbbbcdsfccc      |
+-----------+-----------------+

Expected result sample:

<items>
<phone>222</phone>
<prama4>bbbbcdsfceecc</param4>
<param3>bbbbcdsfceecc</param3>
<param2>bbbbcdsfccc</param2>
</items>

I tried lot of variations of the following query

SELECT atributname,atributvalue  
FROM sampletable FOR XML PATH (''), ROOT ('items');

but results are not good :( should be exactly like in "Expected result sample"

any help

ps Script to create sampletable:

create table sampletable
(atributname varchar(20),
atributvalue varchar(20))


insert into  sampletable (atributname,atributvalue) 
values ('phone','222');

insert into  sampletable (atributname,atributvalue) 
values ('param4','bbbbcdsfceecc');

insert into  sampletable (atributname,atributvalue) 
values ('param3','bbbbcdsfceecc');

insert into  sampletable (atributname,atributvalue) 
values ('param2','bbbbcdsfccc');

Answer 1

That's not how FOR XML works. It's columns that get turned into XML elements, not rows. In order to obtain the expected result, you would need to have columns named phone, param4, and so on - not rows with these values in attributename.

If there are specific elements you want in the XML, you could perform a pivot on the data first, then use FOR XML.

Example of a pivot would be:

SELECT [phone], [param2], [param3], [param4]
FROM
(
    SELECT attributename, attributevalue
    FROM attributes
) a
PIVOT
(
    MAX(attributevalue)
    FOR attributename IN ([phone], [param2], [param3], [param4])
) AS pvt
FOR XML ROOT('items')

Of course the aggregate will only work if attributevalue is a numeric data type. If it's a character-type column, then you'll have some trouble with the pivot, as there are no built-in string aggregates in SQL server AFAIK...

Answer 2

ok finally i have done this in several ways,
but this is simplest version suitable for medium dataset

declare @item  nvarchar(max)

set @item= (SELECT '<' + atributname +'>' +
            cast(atributvalue as nvarchar(max)) +'</' + atributname +'>'
            FROM sampletable FOR XML PATH (''), ROOT ('items'));

select replace(replace(@item,'&lt;','<'),'&gt;','>')