Avoid automatically XML-Tag Creation in SQL-Server

Question

I have got an table in SQL-Server, where I have XML stored in one column.

CREATE TABLE [dbo].[MyTable](
[MyId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[MyContent] [xml] NULL
)

The xml in the column "MyContent" looks like this:

<Account id="1">
    <Name>Bill</Name>
</Account>

Now I want to concatenate all columns to one big xml. I tried to do that with "for xml" function in SQL-Server:

select  
   MyContent 
from MyTable
for xml path('')

The XML, that is created looks like this:

<MyContent>
   <Account id="1">
      <Name>Bill</Name>
   </Account>
</MyContent>
<MyContent>
   <Account id="2">
      <Name>John</Name>
   </Account>
</MyContent>

But that is not what I needed, I need the XML without the created "MyContent" Tags, so what I need is this:

<Account id="1">
   <Name>Bill</Name>
</Account>
<Account id="2">
   <Name>John</Name>
</Account>

Is there any way to avoid the Tag creation for the column name?

Mikael Eriksson · Accepted Answer · 2013-05-27 13:59:55Z

3

Use * as column alias.

Columns with a Name Specified as a Wildcard Character

select  
   MyContent as [*]
from MyTable
for xml path('')

answered May 27, 2013 at 13:59

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devart · Accepted Answer · 2013-05-27 14:01:01Z

0

Try this one -

Query:

INSERT INTO dbo.MyTable(MyContent)
VALUES 
    ('<Account id="1"><Name>Bill</Name></Account>'), 
    ('<Account id="2"><Name>Jack</Name></Account>')

SELECT [*] = MyContent 
FROM dbo.MyTable
FOR XML PATH ('')

Output:

<Account id="1">
  <Name>Bill</Name>
</Account>
<Account id="2">
  <Name>Jack</Name>
</Account>

answered May 27, 2013 at 14:01

Devart

120k23 gold badges172 silver badges189 bronze badges

1 Comment

Chris Over a year ago

Thanks, Mikael was a litle bit faster, so I will accept his answer

Collectives™ on Stack Overflow

Avoid automatically XML-Tag Creation in SQL-Server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related