SQL Server - FOR XML AUTO 添加根节点

这篇文章展示如何通过SQLServer中的 FOR XML AUTO结果中添加XML根节点。

创建测试表

使用以下T-SQL代码创建测试表以及插入测试数据：

create table TestXmlQuery(
ID int identity(1,1) not null,
Name varchar(10)
)
go
insert into [TestXmlQuery] (Name) values('测试1')
insert into [TestXmlQuery] (Name) values('测试2')
insert into [TestXmlQuery] (Name) values('测试3')
insert into [TestXmlQuery] (Name) values('测试4')

查询结果返回XML

使用以下代码查询刚创建的表：

select t.ID as myID,t.Name as myName from [TestXmlQuery] as t for xml auto,type

得到如下结果：

<t myID="1" myName="测试1" />
<t myID="2" myName="测试2" />
<t myID="3" myName="测试3" />
<t myID="4" myName="测试4" />

将列以元素返回

用以下代码将每个列以单独的Element返回，元素的名称为SQL语句中使用的表的别名：

select t.ID as myID,t.Name as myName from [TestXmlQuery] as t for xml auto,elements

得到如下结果：

<t>
<myID>1</myID>
<myName>测试1</myName>
</t>
<t>
<myID>2</myID>
<myName>测试2</myName>
</t>
<t>
<myID>3</myID>
<myName>测试3</myName>
</t>
<t>
<myID>4</myID>
<myName>测试4</myName>
</t>

使用RAW Elements

select t.ID as myID,t.Name as myName from [TestXmlQuery] as t for xml raw

得到如下结果：

<row myID="1" myName="测试1" />
<row myID="2" myName="测试2" />
<row myID="3" myName="测试3" />
<row myID="4" myName="测试4" />

使用RAW Elements以及返回列为元素

select t.ID as myID,t.Name as myName from [TestXmlQuery] as t for xml raw,elements

得到如下结果：

<row>
<myID>1</myID>
<myName>测试1</myName>
</row>
<row>
<myID>2</myID>
<myName>测试2</myName>
</row>
<row>
<myID>3</myID>
<myName>测试3</myName>
</row>
<row>
<myID>4</myID>
<myName>测试4</myName>
</row>

添加XML根节点

select t.ID as myID,t.Name as myName from [TestXmlQuery] as t for xml raw,elements,root('myRoot')

得到如下结果：

<myRoot>
<row>
     <myID>1</myID>
     <myName>测试1</myName>
</row>
<row>
     <myID>2</myID>
     <myName>测试2</myName>
</row>
<row>
     <myID>3</myID>
     <myName>测试3</myName>
</row>
<row>
     <myID>4</myID>
     <myName>测试4</myName>
</row>
</myRoot>

select t.ID as myID,t.Name as myName from [TestXmlQuery] as t for xml raw,type,root('myRoot')

得到如下结果：

<myRoot>
<row myID="1" myName="测试1" />
<row myID="2" myName="测试2" />
<row myID="3" myName="测试3" />
<row myID="4" myName="测试4" />
</myRoot>

select t.ID as myID,t.Name as myName from [TestXmlQuery] as t for xml auto,type,root('myRoot')

得到如下结果

<myRoot>
  <t myID="1" myName="测试1" />
  <t myID="2" myName="测试2" />
  <t myID="3" myName="测试3" />
  <t myID="4" myName="测试4" />
</myRoot>