CODEWITHSUNDEEP
sqlsql-serverxml
Chris
Chris

Reputation: 1642

Avoid automatically XML-Tag Creation in SQL-Server

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?

Upvotes: 2

Views: 88

Answers (2)

Devart
Devart

Reputation: 121922

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>

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Use * as column alias.

Columns with a Name Specified as a Wildcard Character

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

Upvotes: 3

Related Questions