使用嵌套 AUTO 模式查询生成同级

项目
08/29/2023

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例

以下示例显示了如何使用嵌套 AUTO 模式查询来生成同级。生成此类 XML 的其他方式只有这一种，即使用 EXPLICIT 模式。但是，这样做可能会很麻烦。

示例

此查询将构造用于提供销售订单信息的 XML。其中包括：

销售订单表头信息、 SalesOrderID、 SalesPersonID和 OrderDate。 AdventureWorks2022 将此信息存储在 SalesOrderHeader 表中。
销售订单详细信息。这包括所订购的一个或多个产品、单价和订购数量。此信息存储在 SalesOrderDetail 表中。
销售人员信息。这是获得订单的销售人员。 SalesPerson 表提供 SalesPersonID。对于此查询，必须将此表联接到 Employee 表以查找销售人员的姓名。

后面两个不同的 SELECT 查询生成外形略有不同的 XML。

第一个查询生成的 XML 中的 <SalesPerson> 和 <SalesOrderHeader> 显示为 <SalesOrder> 的同级子成员：

SELECT
      (SELECT top 2 SalesOrderID, SalesPersonID, CustomerID,
         (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
           from Sales.SalesOrderDetail
            WHERE  SalesOrderDetail.SalesOrderID =
                   SalesOrderHeader.SalesOrderID
            FOR XML AUTO, TYPE)
        FROM  Sales.SalesOrderHeader
        WHERE SalesOrderHeader.SalesOrderID = SalesOrder.SalesOrderID
        for xml auto, type),
        (SELECT *
         FROM  (SELECT SalesPersonID, EmployeeID
              FROM Sales.SalesPerson, HumanResources.Employee
              WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As
                     SalesPerson
         WHERE  SalesPerson.SalesPersonID = SalesOrder.SalesPersonID
       FOR XML AUTO, TYPE)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
      FROM Sales.SalesOrderHeader, Sales.SalesPerson
      WHERE SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID
     ) as SalesOrder
ORDER BY SalesOrder.SalesOrderID
FOR XML AUTO, TYPE;

在先前的查询中，最外面的 SELECT 语句执行下列操作：

查询在 SalesOrder 子句中指定的行集 FROM。结果是包含一个或多个 <SalesOrder> 元素的 XML。
指定 AUTO 模式和 TYPE 指令。 AUTO 模式将查询结果转换为 XML，且 TYPE 指令返回 xml 类型的结果。
包括两个以逗号分隔的嵌套 SELECT 语句。第一个嵌套 SELECT 语句检索销售订单信息、表头和详细信息，第二个嵌套 SELECT 语句检索销售人员信息。
- 检索 SELECT 、 SalesOrderID和 SalesPersonID的 CustomerID 语句本身包括另一个返回销售订单详细信息的嵌套 SELECT ... FOR XML 语句（使用 AUTO 模式和 TYPE 指令）。

检索销售人员信息的 SELECT 语句查询在 SalesPerson子句中创建的行集 FROM 。若要使用 FOR XML 查询，必须提供在 FROM 子句中生成的匿名行集的名称。在本例中，提供的名称为 SalesPerson。

下面是部分结果：

<SalesOrder>
  <Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  </Sales.SalesOrderHeader>
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</SalesOrder>
...

以下查询生成的销售订单信息基本相同，只是在结果 XML 中，<SalesPerson> 显示为 <SalesOrderDetail> 的同级：

<SalesOrder>
    <SalesOrderHeader ...>
          <SalesOrderDetail .../>
          <SalesOrderDetail .../>
          ...
          <SalesPerson .../>
    </SalesOrderHeader>

</SalesOrder>
<SalesOrder>
  ...
</SalesOrder>

以下是查询语句：

SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT *
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43660
FOR XML AUTO, TYPE;

结果如下：

<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="117">
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>

由于 TYPE 指令将查询结果作为 xml 类型返回，因此，可以使用各种 xml 数据类型方法查询生成的 XML。有关详细信息，请参阅 xml 数据类型方法。在以下查询中，注意：

将先前的查询添加到 FROM 子句。查询结果返回为表。注意添加的 XmlCol 别名。

SELECT 子句对 XmlCol 子句中返回的 FROM 指定 XQuery。 xml 数据类型的 query() 方法用于指定 XQuery。有关更多信息，请参阅 query() 方法（xml 数据类型）。

SELECT XmlCol.query('<Root> { /* } </Root>')
FROM (
SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT *
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID='43659' or SalesOrderID='43660'
FOR XML AUTO, TYPE ) as T(XmlCol);

另请参阅

使用嵌套 FOR XML 查询

Share via

使用嵌套 AUTO 模式查询生成同级

示例

另请参阅

反馈

反馈

其他资源