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>
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.