Move xml column values to external to query xml path

Question

I want to wrap xml from a column in an out query for xml without the column name as an element.

declare @tab table (col xml)
insert @tab
select '<element/>'

select  'val' AS "@att" , col
from  @tab
for xml path ('ROOT')

This gives

<ROOT att="val">
  <col>
    <element />
  </col>
</ROOT>

but I want

<ROOT att="val">
    <element />
</ROOT>

All help is most appreciated.

+3

Jules Mar 10 '11 at 12:00

2 answers

select
  'val' AS "@att",
  (select col)
from  @tab
for xml path ('ROOT')

+2

Mikael eriksson Mar 10 '11 at 12:29

etliens · Accepted Answer · 2011-03-10T20:16:52+0000

A bit cleaner method.

SELECT
    'val' AS [@att], 
    [col] AS [*]
FROM
    @tab
FOR XML PATH ('ROOT');

Move xml column values ​​to external to query xml path