CODEWITHSUNDEEP
sql-serverxmlt-sqlgrouping
Palps
Palps

Reputation: 568

Convert groups of multiple key-value rows to XML

I have a table called userInfo that has data similar to the following:

Id, Field, Value
---------------------
1, FirstName, John
1, LastName, Smith
1, Age, 25
1, Gender, Male
2, FirstName, Jane
2, LastName, Smythe
2, Age, 24
2, Gender, Female

What I need is some T-SQL that will produce a single row for each Id with the following structure:

Row:1

<FieldValues>
<FirstName>John</FirstName>
<LastName>Smith</LastName>
<Age>25</Age>
<Gender>Male</Gender>
</FieldValues>

Row:2

<FieldValues>
<FirstName>Jane</FirstName>
<LastName>Smythe</LastName>
<Age>24</Age>
<Gender>Female</Gender>
</FieldValues>

I have tried a couple of things to get this but can't get figure this out.

Edit: The list of Fields I provided here (i.e. FirstName, LastName, etc) is not a static list of fields. I will be adding and taking away from this list all the time so the query would be able to handle this automatically). Ideally I could use something like FOR XML PATH('FieldValues')

Upvotes: 2

Views: 108

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can build your XML as a string using for xml path('') and then cast to XML.

select T.Id,
       cast('<FieldValues>' + (
                              select '<'+T2.Field+'>'+
                                       (select T2.Value as '*' for xml path(''))+
                                     '</'+T2.Field+'>'
                              from dbo.YourTable as T2
                              where T.Id = T2.Id
                              for xml path(''), type
                              ).value('text()[1]', 'varchar(max)') +
            '</FieldValues>' as xml)  as FieldValues
from dbo.YourTable as T
group by T.Id;

SQL Fiddle

This part (select T2.Value as '*' for xml path('')) is there to take care of characters that needs to be entities in the value like &.

Upvotes: 1

Hogan
Hogan

Reputation: 70523

Here is one way:

SELECT '<FieldValues>'+
       '<FirstName>'+fn.Value +'</FirstName>' +
       '<LastName>'+ln.Value +'</LastName>' +
       '<Age>'+age.Value +'</Age>' +
       '<Gender>'+gender.Value +'</Gender>' +
       '</FieldValues>
FROM (SELECT DISTINCT ID FROM userInfo) t
JOIN userInfo fn ON t.ID = fn.ID and fn.Field = 'FirstName'
JOIN userInfo ln ON t.ID = ln.ID and ln.Field = 'LastName'
JOIN userInfo age ON t.ID = age.ID and age.Field = 'Age'
JOIN userInfo gender ON t.ID = gender.ID and gender.Field = 'Gender'

How this works:

First I create table of just the unique ID numbers.

SELECT DISTINCT ID FROM table

Then I use this table to join back to the main table for each field. (Each of these joins will have only one row per ID.)

JOIN table fn ON t.ID = fn.ID and fn.Field = 'FirstName'
JOIN table ln ON t.ID = ln.ID and ln.Field = 'LastName'
JOIN table age ON t.ID = age.ID and age.Field = 'Age'
JOIN table gender ON t.ID = gender.ID and gender.Field = 'Gender'

Finally I create a string formatted as you need.

'<FieldValues>'+
'<FirstName>'+fn.Value +'</FirstName>' +
'<LastName>'+ln.Value +'</LastName>' +
'<Age>'+age.Value +'</Age>' +
'<Gender>'+gender.Value +'</Gender>' +
'</FieldValues>

An additional note: It is recommended to not use Camel Case on your xml since xml is case sensitive any use of case is a pain -- most just use all lower case.

Upvotes: 0

Related Questions