access_time 12 years ago language中文
more_vert

SQL Server SQL for xml auto 添加根节点

visibility 437 comment 0

今天从数据库中查询出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

得到如下结果:

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

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>

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" />

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>

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>

info Last modified by Raymond 7 years ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 727
thumb_up 0
access_time 9 months ago
visibility 405
thumb_up 0
access_time 8 years ago