By using this site, you acknowledge that you have read and understand our Cookie policy, Privacy policy and Terms .
access_time 11 years ago visibility383 comment 0 language中文

今天从数据库中查询出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 at 6 years ago * This page is subject to Site terms.

More from Kontext

visibility 13613
thumb_up 0
access_time 6 years ago

SQL Server Compact 4.0 (CE 4.0) is a free SQL Server embedded database ideal for building standalone and occasionally connected applications for mobile devices, desktops, Web clients and others. In one of my projects, I used it as the database for logging errors, which assumes the errors will onl...

open_in_new View

visibility 684
thumb_up 0
access_time 6 years ago

SQL Server provides a batch of great features to build robust, high-performance and scalable data solutions. ...

open_in_new View

visibility 1141
thumb_up 0
access_time 6 years ago

Serial: An Introduction to SQL Server Features Case Scenario In the previous articles of this serial, I’ve introduced how to use SQL Server as database for online transaction proces...

open_in_new View

visibility 4097
thumb_up 0
access_time 6 years ago

Serial: An Introduction to SQL Server Features Case Scenario For this ETL project, the requirements are listed below: Sales data will be pushed to specified shared fold...

open_in_new View

info About author

Kontext Column

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.

Learn more arrow_forward
info Follow us on Twitter to get the latest article updates. Follow us