使用 AUTO 模式

项目
12/15/2008

如主题使用 FOR XML 检索 XML 文档中所述，AUTO 模式将查询结果作为嵌套 XML 元素返回。这不能较好地控制从查询结果生成的 XML 的形式。如果要生成简单的层次结构，AUTO 模式查询很有用。但是，使用 EXPLICIT 模式和使用 PATH 模式在确定从查询结果生成的 XML 的形式方面提供了更好的控制和更大的灵活性。

在 FROM 子句内，每个在 SELECT 子句中至少有一列被列出的表都表示为一个 XML 元素。如果在 FOR XML 子句中指定了可选的 ELEMENTS 选项，SELECT 子句中列出的列将映射到属性或子元素。

生成的 XML 中的 XML 层次结构（即元素嵌套）基于由 SELECT 子句中指定的列所标识的表的顺序。因此，在 SELECT 子句中指定的列名的顺序非常重要。最左侧第一个被标识的表形成所生成的 XML 文档中的顶级元素。由 SELECT 语句中的列所标识的最左侧第二个表形成顶级元素内的子元素，依此类推。

如果 SELECT 子句中列出的列名来自由 SELECT 子句中以前指定的列所标识的表，该列将作为已创建的元素的属性添加，而不是在层次结构中打开一个新级别。如果已指定 ELEMENTS 选项，该列将作为属性添加。

例如，执行以下查询：

SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO

下面是部分结果：

<Cust CustomerID="1" CustomerType="S">
  <OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</Cust>
...

对于 SELECT 子句，注意下列内容：

CustomerID 引用 Cust 表。因此，创建一个 <Cust> 元素，CustomerID 作为其属性添加。
接下来的三列 OrderHeader.CustomerID、OrderHeader.SaleOrderID 和 OrderHeader.Status 引用 OrderHeader 表。因此，为 <Cust> 元素添加 <OrderHeader> 子元素，这三列作为 <OrderHeader> 的属性添加。
接着，Cust.CustomerType 列再次引用 Cust 表，该表已由 Cust.CustomerID 列标识。因此，不创建新元素，而是为以前创建的 <Cust> 元素添加 CustomerType 属性。
查询为表名指定别名。这些别名显示为相应的元素名。
需要使用 ORDER BY 对一个父级下的所有子级分组。

下面的查询与上一个查询类似，不同的是 SELECT 子句先指定 OrderHeader 表中的列，再指定 Cust 表中的列。因此，首先创建 <OrderHeader> 元素，然后为该元素添加 <Cust> 子元素。

select OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status,
       Cust.CustomerID, 
       Cust.CustomerType
from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
where Cust.CustomerID = OrderHeader.CustomerID
for xml auto

下面是部分结果：

<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
  <Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...

如果在 FOR XML 子句中添加了 ELEMENTS 选项，将返回以元素为中心的 XML。

SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS

下面是部分结果：

<Cust>
  <CustomerID>1</CustomerID>
  <CustomerType>S</CustomerType>
  <OrderHeader>
    <CustomerID>1</CustomerID>
    <SalesOrderID>43860</SalesOrderID>
    <Status>5</Status>
  </OrderHeader>
   ...
</Cust>
...

在此查询中，因为 CustomerID 是表的主键，所以在创建 <Cust> 元素的过程中会逐行比较 CustomerID 值。如果未将 CustomerID 标识为表的主键，将逐行比较所有列值（此查询中的 CustomerID 和 CustomerType）。如果值不同，将向 XML 添加新的 <Cust> 元素。

在比较这些列值时，如果要比较的任何列是 text、ntext、image 或 xml 类型，即使它们的值可能相同，FOR XML 也将认为它们是不同的，并且不对其进行比较。这是因为不支持大型对象的比较。这些元素将被添加到每个选定行的结果中。请注意，会比较 (n)varchar(max) 和 varbinary(max) 类型的列。

如果 SELECT 子句中的某列无法与 FROM 子句中标识的任何表相关联（例如，该列是聚合列或计算列的情况下），则该列在列表中出现时，将添加到 XML 文档的最深嵌套级别中。如果该列作为 SELECT 子句的第一列出现，将被添加到顶级元素。

如果 SELECT 子句中指定了星号 (*) 通配符，则以前面所述的方式根据查询引擎所返回的行的顺序确定嵌套。

如果查询中指定了 BINARY BASE64 选项，则以 base64 编码格式返回二进制数据。默认情况下，如果未指定 BINARY BASE64 选项，则 AUTO 模式支持二进制数据的 URL 编码。也就是说，不返回二进制数据，而返回执行查询的数据库的虚拟根目录的相对 URL 的引用。通过使用 SQLXML ISAPI dbobject 查询，此引用可用于访问后续操作中的真实二进制数据。查询必须提供足够的信息（如主键列），才能标识图像。

在指定查询的过程中，如果对视图的二进制列使用了别名，将在二进制数据的 URL 编码中返回别名。在后续操作中，别名没有意义，也不能用 URL 编码检索图像。因此，在使用 FOR XML AUTO 模式查询视图时不要使用别名。

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

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

下面的示例说明了确定生成的 XML 的形式的 AUTO 模式试探方法：

SELECT T1.Id, T2.Id, T1.Name
FROM   T1, T2
WHERE ...
FOR XML AUTO
ORDER BY T1.Id

如果未指定表 T1 的键，若要确定新 <T1> 元素的开始位置，需要比较 T1 中除 ntext、text、image 和 xml 类型以外的所有列的值。然后，假定 Name 列是 nvarchar(40) 类型，SELECT 语句将返回以下行集：

T1.Id  T1.Name  T2.Id
-----------------------
1       Andrew    2
1       Andrew    3
1       Nancy     4

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

下面是返回的 XML：

<T1 Id="1" Name="Andrew">
    <T2 Id="2" />
    <T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
      <T2 Id="4" />
</T>

现在，假定 Name 列是 text 类型。AUTO 模式试探方法不比较此类型的值，而是认为这些值不相同。这将导致 XML 生成结果显示如下：

<T1 Id="1" Name="Andrew" >
  <T2 Id="2" />
</T1>
<T1 Id="1" Name="Andrew" >
  <T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
  <T2 Id="4" />
</T1>

示例

下列示例说明了 AUTO 模式的使用。下面的许多查询是根据 ProductModel 表的 Instructions 列中存储的自行车生产说明 XML 文档指定的。有关 XML 说明的详细信息，请参阅 AdventureWorks 数据库中的 xml 数据类型表示形式。

A.检索客户、订单和订单详细信息

此查询检索特定客户的客户、订单和订单详细信息。

SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       Detail.SalesOrderID, Detail.LineTotal,Detail.ProductID, 
       Product.Name,
       Detail.OrderQty
FROM Sales.Customer Cust, 
     Sales.SalesOrderHeader OrderHeader,
     Sales.SalesOrderDetail Detail,
     Production.Product Product
WHERE Cust.CustomerID = OrderHeader.CustomerID
AND   OrderHeader.SalesOrderID = Detail.SalesOrderID
AND   Detail.ProductID = Product.ProductID
AND   (Cust.CustomerID=117 or Cust.CustomerID=442)
ORDER BY OrderHeader.CustomerID,
         OrderHeader.SalesOrderID
FOR XML AUTO

由于查询标识了 Cust、OrderHeader、Detail 和 Product 表别名，因此 AUTO 模式生成相应的元素。同样，由 SELECT 子句中指定的列所标识的表的顺序确定这些元素的层次结构。

下面是部分结果：

<Cust CustomerID="117">
  <OrderHeader CustomerID="117" SalesOrderID="43660">
    <Detail SalesOrderID="43660" LineTotal="874.794000" ProductID="758" OrderQty="1">
      <Product Name="Road-450 Red, 52" />
    </Detail>
    <Detail SalesOrderID="43660" LineTotal="419.458900" ProductID="762" OrderQty="1">
      <Product Name="Road-650 Red, 44" />
    </Detail>
  </OrderHeader>
  <OrderHeader CustomerID="117" SalesOrderID="47660">
    <Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">
      <Product Name="Road-650 Black, 58" />
    </Detail>
  </OrderHeader>
  <OrderHeader CustomerID="117" SalesOrderID="49857">
    <Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">
      <Product Name="Women's Tights, S" />
    </Detail>
  </OrderHeader>
   ...
</Cust>

B.指定 GROUP BY 和聚合函数

以下查询将返回各个客户 ID 以及客户已请求的订单数。

SELECT I.CustomerID, count(*) as NoOfOrders
from Sales.Individual I,Sales.SalesOrderHeader SOH
WHERE I.CustomerID = SOH.CustomerID
GROUP BY I.CustomerID
FOR XML AUTO

下面是部分结果：

<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...

C.在 AUTO 模式下指定计算列

此查询返回串联的各个客户名以及订单信息。因为计算列被分配到在该点（在此例中是 <SOH>）出现的最内层级别，因此，串联的客户名在结果中作为 <SOH> 元素的属性添加。

select C.FirstName + ' ' + C.LastName as Name,
       SOH.SalesOrderID
from Sales.Individual I, Person.Contact C,
     Sales.SalesOrderHeader SOH
where I.ContactID = C.ContactID
AND   I.CustomerID = SOH.CustomerID
FOR XML AUTO

下面是部分结果：

<SOH Name="David Robinett" SalesOrderID="53647" />
<SOH Name="Rebecca Robinson" SalesOrderID="72188" />

若要检索具有 Name 属性（包含销售订单表头信息，并将每条信息作为一个子元素）的 <IndividualCustomer> 元素，应使用嵌套的 SELECT 子句重写查询。内部 SELECT 子句创建临时的 IndividualCustomer 表，此表具有计算列，其中包含各个客户的名称。然后，此表与 SalesOrderHeader 表联接以获得结果。

请注意，Sales.Individual 表存储有单个客户信息，其中包括该客户的 ContactID 值。然后，ContactID 被用于从 Person.Contact 表中查找联系人姓名。

SELECT IndividualCustomer.Name, SOH.SalesOrderID
FROM (SELECT FirstName+ ' '+LastName as Name, I.CustomerID
      FROM Sales.Individual I, Person.Contact C
      WHERE I.ContactID = C.ContactID) IndividualCustomer
left outer join  Sales.SalesOrderHeader SOH
ON IndividualCustomer.CustomerID = SOH.CustomerID
ORDER BY IndividualCustomer.CustomerID, SOH.CustomerID
FOR XML AUTO

下面是部分结果：

<IndividualCustomer Name="Jon Yang">
  <SOH SalesOrderID="43793" />
  <SOH SalesOrderID="51522" />
  <SOH SalesOrderID="57418" />
</IndividualCustomer>
...
...

D.返回二进制数据

下面的查询返回 Employees 表中的雇员照片。Photo 是 Employees 表中的 image 列。默认情况下，AUTO 模式向二进制数据返回执行查询的数据库的虚拟根目录的相对 URL 的引用。必须指定 EmployeeID 键特性，才能标识图像。如此示例中所示，检索图像引用时，还必须在 SELECT 子句中指定表的主键，才能唯一标识行。

SELECT ProductPhotoID, ThumbNailPhoto
FROM   Production.ProductPhoto 
WHERE ProductPhotoID=70
FOR XML AUTO

结果如下：

-- result
<Production.ProductPhoto 
    ProductPhotoID="70" 
    ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />

用 BINARY Base64 选项执行上述查询。查询以 base64 编码格式返回二进制数据。

SELECT ProductPhotoID, ThumbNailPhoto
FROM   Production.ProductPhoto 
WHERE ProductPhotoID=70
FOR XML AUTO, BINARY BASE64

结果如下：

-- result
<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />

默认情况下，使用 AUTO 模式检索二进制数据时，将返回执行查询的数据库的虚拟根目录的相对 URL 的引用，而不返回二进制数据。如果未指定 BINARY BASE64 选项，也会出现这种情况。

当 AUTO 模式返回不区分大小写的数据库（查询中指定的表名或列名与数据库中的表名或列名不匹配）中的二进制数据的 URL 引用时，将执行查询。但是，引用中返回结果的大小写将不一致。例如：

SELECT PRODUCTPHOTOID, THUMBNAILPHOTO
FROM   Production.PRODUCTPHOTO 
WHERE PRODUCTPHOTOID=70
FOR XML AUTO

结果如下：

<Production.PRODUCTPHOTO 
        PRODUCTPHOTOID="70" 
        THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />

这可能成为一个问题，尤其是对区分大小写的数据库执行 dbobject 查询时。若要避免这个问题，查询中指定的表名或列名的大小写应该与数据库中表名或列名的大小写一致。

E.了解编码

下面的示例显示了结果中出现的各种编码。

创建下表：

CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50))

将下列数据添加到表中：

INSERT INTO [Special Chars] values ('&', 0x20)
INSERT INTO [Special Chars] values ('#', 0x20)

下面的查询将返回该表中的数据。指定了 FOR XML AUTO 模式。二进制数据作为引用返回。

SELECT * FROM [Special Chars] FOR XML AUTO

结果如下：

<Special_x0020_Chars 
Col1="#"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='#']/@Col_x0023__x0026_2"
/>
<Special_x0020_Chars 
Col1="&amp;" 
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&amp;']/@Col_x0023__x0026_2"
/>

下面是对结果中的特殊字符进行编码的过程：

通过使用相应的 Unicode 字符的十六进制值，对查询结果中返回的元素名及属性名中的特殊 XML 和 URL 字符进行编码。在上面的结果中，元素名 <Special Chars> 作为 <Special_x0020_Chars> 返回。属性名 <Col#&2> 作为 <Col_x0023__x0026_2> 返回。XML 和 URL 特殊字符都进行了编码。
如果元素值或属性值包含 5 个标准 XML 字符实体（'、""、<、> 和 &）中的任何一个，将始终使用 XML 字符编码对这些特殊 XML 字符进行编码。在上面的结果中，属性 <Col1> 的值中的 & 值被编码为 &。但是，# 字符仍保留为 #，因为它是有效的 XML 字符，而不是特殊的 XML 字符。
如果元素值或属性值包含 URL 中有特殊意义的任何特殊 URL 字符，则只能在 DBOBJECT URL 值中对它们进行编码，而且只有当该特殊字符是表名或列名的一部分时，才会对它们进行编码。在结果中，作为表名 Col#&2 的一部分的字符 # 在 DBOJBECT URL 中编码为 _x0023_。

请参阅

使用 AUTO 模式

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

示例

A.检索客户、订单和订单详细信息

B.指定 GROUP BY 和聚合函数

C.在 AUTO 模式下指定计算列

D.返回二进制数据

E.了解编码

请参阅

参考

其他资源

帮助和信息

其他资源