避免在SQL-Server中自动创建xml标记

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

我在SQL-Server中有一个表，其中XML存储在一个列中。

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:

“MyContent”列中的xml如下所示:

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

现在我要将所有列连接到一个大xml。我尝试使用SQL-Server中的“for xml”函数来实现这一点:

select  
   MyContent 
from MyTable
for xml path('')

The XML, that is created looks like this:

创建的XML如下所示:

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

但这不是我需要的，我需要没有创建的“MyContent”标签的XML，所以我需要的是:

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

有什么方法可以避免为列名创建标记吗?

2 个解决方案

#1

Use * as column alias.

使用*作为列别名。

Columns with a Name Specified as a Wildcard Character

指定为通配符的名称的列

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

#2

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>

#1