Создание братьев и сестер с помощью вложенного запроса режима AUTO

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В следующем примере показано, как создавать элементы с общим родителем при помощи вложенного запроса в режиме 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. Результатом является XML с одним или несколькими <SalesOrder> элементами.

  • Указывает режим 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>
...

Следующий запрос создает те же сведения о заказах на продажу, за исключением того, <SalesPerson> что в результирующем XML-коде отображается как брат <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 определяет запрос XQuery к XmlCol , возвращаемому в предложении FROM . Метод query() типа данных XML используется при указании XQuery. Дополнительные сведения см. в статье query() Method (xml Data Type).

    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);

См. также