SQL Server SQL for xml auto 添加根节点

来源：互联网发布：做自己知乎编辑：程序博客网时间：2024/04/30 01:22

今天从数据库中查询出xml，同时添加一个根节点

做了如下测试：

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')

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

得到如下结果：

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

得到如下结果：

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

得到如下结果：

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

得到如下结果：

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')

得到如下结果：

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

得到如下结果

转自:http://hi.baidu.com/1987raymond/blog/item/5e08f3fcfde49a88b801a049.html