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 xml
auto

结果:

<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 模式试探方法会比较相邻行中的列值。ntexttextimage 和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 xml
auto

结果:

<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查询。



  1. SQL SERVER中XML查询:FOR XML指定RAW
  2. SQL SERVER中XML查询:FOR XML指定AUTO
  3. SQL SERVER中XML查询:FOR XML指定EXPLICIT
  4. SQL SERVER中XML查询:FOR XML指定PATH
  5. 关于XML类型,请参考:http://blog.csdn.net/leewhoee/article/details/8571286
  6. 关于XML索引,请参考:http://blog.csdn.net/leewhoee/article/details/8579743




  1. SQL SERVER中XML查询:FOR XML指定RAW
  2. SQL SERVER中XML查询:FOR XML指定AUTO
  3. SQL SERVER中XML查询:FOR XML指定EXPLICIT
  4. SQL SERVER中XML查询:FOR XML指定PATH
  5. 关于XML类型,请参考:http://blog.csdn.net/leewhoee/article/details/8571286
  6. 关于XML索引,请参考:http://blog.csdn.net/leewhoee/article/details/8579743

SQL SERVER中XML查询:FOR XML指定AUTO,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。