sql server - SQL FOR XML use values as column names -

the xml command uses column names create xml nodes. need nodes named after values i'm getting database. far know can't such

select key section

section         | key                | value ----------------------------------------------------------- pageaddproduct  |   errordateformat  |  incorrect value pageaddproduct  |   errornotselected |  please select value widgetlogin     |   title            |  connexion widgetlogin     |   maillabel        |  mail 

this desired xml output

<resources>   <widgetlogin>     <title>connexion</title>     <maillabel>mail</maillabel>   </widgetlogin> </resources> 

not sure if can xml. appreciated.

you can build xml string , cast xml.

this sample code work in sql server 2012 since using concat function can rewritten use + instead.

select cast(concat('<resources>',                    (                    select concat('<',t1.section,'>',                                  (                                  select concat('<',t2.[key],'>',                                                (select t2.value xml path('')),                                                '</',t2.[key],'>')                                  t t2                                  t1.section = t2.section                                  xml path(''), type                                  ).value('text()[1]', 'nvarchar(max)'),                                  '</',t1.section,'>')                    t t1                    group t1.section                    xml path(''), type                    ).value('text()[1]', 'nvarchar(max)'),                    '</resources>') xml) 

sql fiddle