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

Question

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>

Answer 1

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.

Answer 2

The solution I've come up with after a bit of experimentation is to use the query method as a sort of no-op to avoid the automatic naming.

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

The concept that led me to this was to query for all of the attributes on the innerRoot and the elements. In my experimentation however, I noticed that when specifying the query that the col3 name was no longer being used as the name.

---

One gripe that I have about XML in SQL Server in general is how the syntax is combined with the traditional SQL syntax that many developers such as myself are accustomed with so it's not always easy to now the overloaded concepts such as an unnamed element should be interpreted.