mysql for xml_mssql for xml path使用

准备工作：

CREATE TABLE [dbo].[Students](

[id] [int] IDENTITY(1,1) NOT NULL,

[names] [varchar](50) NULL,

[hobby] [varchar](50) NULL

)

insert into students values('张三','书法'),('张三','篮球'),('张三','台球'),('李四','书法'),('李四','唱歌'),('李四','足球'),('李四','乒乓球')

for xml path

把查询结果用xml表现出来。

1 正常的查询：

select * from Students

结果是：

id names hobby

----------- --------------------------------------------------

1 张三 书法

2 张三 篮球

3 张三 台球

4 李四 书法

5 李四 唱歌

6 李四 足球

7 李四 乒乓球

返回的是一个数据表格(多条)。

2 用xml表现

select * from Students for xml path

结果是：

XML_F52E2B61-18A1-11d1-B105-00805F49916B

------------------------------------------------------------------------------------

1张三书法2张三篮球

>3张三台球4李四书法

5李四

也是一个数据表格(单条)

把查询结果做为xml数据格式返回了一行数据。

3 通过使用path()参数，可改变列节点：

select * from Students for xml path('')

结果：

XML_F52E2B61-18A1-11d1-B105-00805F49916B

-----------------------------------------------------------------------------------

1张三书法2张三篮球

3张三台球4李四书法

5李四唱歌6李四足球

使用path(‘’)空参数，去掉了row节点，直接显示的是两个属性字段。

4 自定义节点

select * from Students for xml path('selfNode')

结果如下：

XML_F52E2B61-18A1-11d1-B105-00805F49916B

--------------------------------------------------------------------------------------------

1张三书法

2张三篮球

3张三台球

4李四书法

总的来说，for xml path返回的是一条数据(一个xml字串)。所以，这个可以作为查询中的一列。如下：

select distinct aaa.names,hobby=(

SELECT hobby+',' FROM students

WHERE names=aaa.names

FOR XML PATH('')

)

from students aaa

结果如下：

names hobby

--------------------------------------------------

李四 书法,唱歌,足球,乒乓球,

张三 书法,篮球,台球,

从students表中查询names，且各自的hobby，hobby是多条记录，通过xml 整合到一条记录，并使用逗点分隔。

上面的语句中有一句：where names=aaa.names，因为for xml path返回的是一条数据字串，不能进行查询条件的关联，即不能做为数据表与其它表进行关联。

它只返回一行一列，且for xml path都整合到一字符串中而没有其它字段，所以不能与其它表进行联合查询。但可以在for xml之前进行联合查询，如下：

select distinct aaa.names,hobby=(

select hobby+',' FROM students

where names=aaa.names

for xml path('')

)   from students aaa

for xml path部分的结果做为一个字段显示，而粗体部分表示把每个names中的hobby字段通过逗号连成一个字段，如下：

names hobby

--------------------------------------------------

李四 书法,唱歌,足球,乒乓球,

张三 书法,篮球,台球,

未使用distinct，结果如下：

names hobby

--------------------------------------------------

张三 书法,篮球,台球,

张三 书法,篮球,台球,

张三 书法,篮球,台球,

李四 书法,唱歌,足球,乒乓球,

李四 书法,唱歌,足球,乒乓球,

李四 书法,唱歌,足球,乒乓球,

李四 书法,唱歌,足球,乒乓球,

把重复的去掉，可通过group或distinct去掉

另外：对于可容忍的脏读而不死锁的with onlock在mssql中可在表后边使用with(nolock)脏读；今天查了一下，对于mysql来说，不需要使用with nolock，会自动使用nolock. 。这个我自己未做过测试，大概只是了解一下。