SQL SERVER中XML查询：FOR XML指定AUTO

前言

在SQL SERVER中，XML查询可以指定RAW，AUTO，EXPLICIT，PATH。本文用一些实例介绍SQL SERVER中指定AUTO的XML查询。

基础示例

片断1：

with TestXml

as

(

select 1 as id,N'LeeWhoeeUniversity' as name

union all

select 2,N'DePaul'

union all

select 3 ,null

)

select id,name from testxml for xml auto

结果：

用表名做元素名称，即替代RAW模式中的“row”。

下面看多表的查询（片断2）：

with [order]

as

(

select 122 as orderid, 1 as productid,10 as quantity

union all

select 123,1 as productid,100 as quantity

union all

select 124,2,20

union all

select 125,3 ,5

),

product

as

(

select 1 as id,N'LeeWhoeeUniversity' as name

union all

select 2,N'DePaul'

)

select * from product,[order] where [order].productid=product.id for xmlauto

结果：

表名顺序敏感

（见上面查询中粗体部分）

如果把product和order换一下位置，片断3：

with [order]

as

(

select 122 as orderid, 1 as productid,10 as quantity

union all

select 123,1 as productid,100 as quantity

union all

select 124,2,20

union all

select 125,3 ,5

),

product

as

(

select 1 as id,N'LeeWhoeeUniversity' as name

union all

select 2,N'DePaul'

)

select * from [order],product where [order].productid=product.id for xml auto

结果：

当然，AUTO模式同样也可以指定ELEMENTS，BINARY BASE64，同RAW。(SQL SERVER中XML查询：FOR XML指定RAW)

返回的 XML 成形过程中的 AUTO 模式试探方法

AUTO 模式根据查询决定返回的 XML 的形式。 在决定嵌套元素的方式时，AUTO 模式试探方法会比较相邻行中的列值。ntext、text、image 和xml 类型以外的所有类型的列都会进行比较。(n)varchar(max) 和varbinary(max) 类型的列也会进行比较。

上面的第一个指定AUTO的SQL语句（片断2）结果集为：

id name orderid productid quantity

1 LeeWhoeeUniversity 122 1 10

1 LeeWhoeeUniversity 123 1 100

2 DePaul 124 2 20

AUTO 模式试探方法将比较表 product 的所有值（Id 列和 Name 列）。因为前两行的 Id 列和 Name 列具有相同的值，所以向结果中添加了一个具有两个 子元素的 元素。

text类型的特殊

如果把Name 列改为 text 类型。 AUTO 模式试探方法不比较此类型的值， 而是认为这些值不相同。

见下面代码片断4：

declare @order table(orderid int,productid int,quantity int)

declare @product table(id int,name text)

insert into @order

select 122 as orderid, 1 as productid,10 as quantity

union all

select 123,1 as productid,100 as quantity

union all

select 124,2,20

union all

select 125,3 ,5

insert into @product

select 1 ,N'LeeWhoeeUniversity'

union all

select 2,N'DePaul'

select * from @product as product,@order as [order] where [order].productid=product.id for xmlauto

结果：

上面结果中name同为LeeWhoeeUniversity的项被分成两个product。

结果集排序对AUTO试探的影响

再看第一个指定AUTO的SQL语句，但是更改了orderid为使其结果集中相同id和name的项不连在一起：

with [order]

as

(

select 122 as orderid, 1 as productid,10 as quantity

union all

select 125,1 as productid,100 as quantity

union all

select 123,2,20

union all

select 124,3 ,5

),

product

as

(

select 1 as id,N'LeeWhoeeUniversity' as name

union all

select 2,N'DePaul'

)

select * from product,[order] where [order].productid=product.id

order by orderid

结果：

id name orderid productid quantity

1 LeeWhoeeUniversity 122 1 10

2 DePaul 123 2 20

1 LeeWhoeeUniversity 125 1 100

然后进行指定AUTO的XML查询(即语句上添加for xml auto)，AUTO模式试探将生成以下结果：

这样相同id和name的product没有连在一起。

总结

以上对指定AUTO的XML查询就介绍完了，下一篇文章将继续用实例介绍SQL SERVER中的XML查询：指定EXPLICIT查询。

SQL SERVER中XML查询：FOR XML指定RAWSQL SERVER中XML查询：FOR XML指定AUTO

SQL SERVER中XML查询：FOR XML指定EXPLICIT

SQL SERVER中XML查询：FOR XML指定PATH关于XML类型

更多信息请查看IT技术专栏