SQL FOR XML use values as column names

Question

The FOR XML command uses column names to create the XML nodes. I need my nodes to be named after values I'm getting from the database. As far as I know you can't do something such as

SELECT Key AS Section

SECTION         | KEY                | VALUE
-----------------------------------------------------------
PageAddProduct  |   ErrorDateFormat  |  Incorrect value
PageAddProduct  |   ErrorNotSelected |  Please select value
WidgetLogin     |   Title            |  Connexion
WidgetLogin     |   MailLabel        |  Mail

This is the desired XML output

<Resources>
  <WidgetLogin>
    <Title>Connexion</Title>
    <MailLabel>Mail</MailLabel>
  </WidgetLogin>
</Resources>

Not sure if you can get this with For Xml. Help would be much appreciated.

Mikael Eriksson · Accepted Answer · 2014-06-27 11:28:08Z

You can build your XML as a string and then cast to XML.

This sample code will work in SQL Server 2012 since it is using the concat function but it can easily be rewritten to use + instead.

select cast(concat('<Resources>',
                   (
                   select concat('<',T1.SECTION,'>',
                                 (
                                 select concat('<',T2.[KEY],'>',
                                               (select T2.VALUE for xml path('')),
                                               '</',T2.[KEY],'>')
                                 from T as T2
                                 where T1.SECTION = T2.SECTION
                                 for xml path(''), type
                                 ).value('text()[1]', 'nvarchar(max)'),
                                 '</',T1.SECTION,'>')
                   from T as T1
                   group by T1.SECTION
                   for xml path(''), type
                   ).value('text()[1]', 'nvarchar(max)'),
                   '</Resources>') as xml)

SQL Fiddle

Umesh · Accepted Answer · 2014-06-27 09:18:09Z

0

You can use FOR XML like below:

SELECT Section as Title, Value as MailLabel
FROM table
FOR XML PATH('WidgetLogin'), ROOT('Resources'), TYPE

answered Jun 27, 2014 at 9:18

Umesh

2,74221 silver badges24 bronze badges

1 Comment

ThunderDev Over a year ago

That will work specifically for the 'WidgetLogin' section, I can potentially have hundreds of sections and values.

Collectives™ on Stack Overflow

SQL FOR XML use values as column names

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related