sql-serverxmlt-sqlselect-for-xml

How to output raw xml within an XML projection in SQL Server without introducing an extra xml root element


Given the below T-SQL snippet which attempts to constructs XML.

declare @table table
(
    col1 varchar(max),
    col2 varchar(max),
    col3 xml
)

declare @someXml xml = '
<innerRoot a="b">
    <child>1</child>
    <child>2</child>
    <child>3</child>
</innerRoot>
'

insert into @table values ('VALUE1', 'VALUE2', @someXml)

select 
    t.col1 as '@attribute1',
    t.col2 as '@attribute2',
    t.col3 as UnwantedElement
from @table as t
for xml path('Root'), type

The resulting XML is:

<Root attribute1="VALUE1" attribute2="VALUE2">
  <UnwantedElement>
    <innerRoot a="b">
      <child>1</child>
      <child>2</child>
      <child>3</child>
    </innerRoot>
  </UnwantedElement>
</Root>

How do I get the same output without UnwantedElement so that it looks like the example below.

<Root attribute1="VALUE1" attribute2="VALUE2">
  <innerRoot a="b">
    <child>1</child>
    <child>2</child>
    <child>3</child>
  </innerRoot>
</Root>

Solution

  • I think you can do that:

    declare @table table
    (
        col1 varchar(max),
        col2 varchar(max),
        col3 xml
    )
    
    declare @someXml xml = '
    <innerRoot a="b">
        <child>1</child>
        <child>2</child>
        <child>3</child>
    </innerRoot>
    '
    
    insert into @table values ('VALUE1', 'VALUE2', @someXml)
    
    select 
        t.col1 as '@attribute1',
        t.col2 as '@attribute2',
        t.col3 as [*]
    from @table as t
    for xml path('Root'), type
    

    Here msdn you can find the documentation for the Wildcard as the column name.