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

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 

結果：

<testxml id="1" name="LeeWhoeeUniversity" />
<testxml id="2" name="DePaul" />
<testxml id="3" />

用表名做元素名稱，即替代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 id="1" name="LeeWhoeeUniversity">
  <order orderid="122" productid="1" quantity="10" />
  <order orderid="123" productid="1" quantity="100" />
</product>
<product id="2" name="DePaul">
  <order orderid="124" productid="2" quantity="20" />
</product>

表名順序敏感 （見上面查詢中粗體部分）

如果把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

結果：

<order orderid="122" productid="1" quantity="10">
  <product id="1" name="LeeWhoeeUniversity" />
</order>
<order orderid="123" productid="1" quantity="100">
  <product id="1" name="LeeWhoeeUniversity" />
</order>
<order orderid="124" productid="2" quantity="20">
  <product id="2" name="DePaul" />
</order>

當然，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 列具有相同的值，所以向結果中添加了一個具有兩個 <order> 子項目的 <product> 元素。

<product id="1" name="LeeWhoeeUniversity">
  <order orderid="122" productid="1" quantity="10" />
  <order orderid="123" productid="1" quantity="100" />
</product>
<product id="2" name="DePaul">
  <order orderid="124" productid="2" quantity="20" />
</product>

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

結果：

<product id="1" name="LeeWhoeeUniversity">
  <order orderid="122" productid="1" quantity="10" />
</product>
<product id="1" name="LeeWhoeeUniversity">
  <order orderid="123" productid="1" quantity="100" />
</product>
<product id="2" name="DePaul">
  <order orderid="124" productid="2" quantity="20" />
</product>

上面結果中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模式試探將產生以下結果：

<product id="1" name="LeeWhoeeUniversity">
  <order orderid="122" productid="1" quantity="10" />
</product>
<product id="2" name="DePaul">
  <order orderid="123" productid="2" quantity="20" />
</product>
<product id="1" name="LeeWhoeeUniversity">
  <order orderid="125" productid="1" quantity="100" />
</product>

這樣相同id和name的product沒有連在一起。

總結
以上對指定AUTO的XML查詢就介紹完了，下一篇文章將繼續用執行個體介紹SQL SERVER中的XML查詢：指定EXPLICIT查詢。