wrap xml column values into outer for xml path query

• Jules Bonnot

  SSCertifiable

  Points: 5488

  More actions

  March 10, 2011 at 4:57 am

  #235924

  I want to wrap the xml from a column into an out for xml path query 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 most appreciated.

  www.sql-library.com[/url]

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  March 10, 2011 at 5:06 am

  #1296998

  There's probably a better way, but doing a meaningless cast or similar so that the output column has no name acheives this result:

  declare @tab table (col xml)

  insert @tab

  select '<element/>'

  select 'val' AS "@att" , CAST(col AS XML)

  from @tab

  for xml PATH ('ROOT')