Формирование одноуровневых элементов с помощью вложенного запроса в режиме AUTO
В следующем примере показано, как создавать элементы с общим родителем при помощи вложенного запроса в режиме AUTO. Единственный способ создать такой XML — использовать режим EXPLICIT. Однако пользоваться этим способом не всегда удобно.
Пример
Этот запрос создает XML, который предоставляет сведения о заказах на продажу. Это включает следующее:
В базе данных База данных AdventureWorks2008R2 заголовки данных о заказах продаж SalesOrderID, SalesPersonID и OrderDate хранятся в таблице SalesOrderHeader.
Дополнительные сведения о заказе на продажу. Она включает один или несколько заказанных продуктов, цену за штуку и заказанное количество. Эти сведения хранятся в таблице SalesOrderDetail.
Сведения о менеджере по продажам. Сведения о менеджере по продажам, принявшем заказ.
Два отдельных запроса, показанные ниже, создают XML-документы с небольшими различиями в формате данных.
Первый запрос создает XML, в котором элемент <SalesOrderHeader> является потомком общего родителя <SalesOrder>:
USE AdventureWorks2008R2;
GO
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)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
FROM Sales.SalesOrderHeader, Sales.SalesPerson
WHERE SalesOrderHeader.SalesPersonID = SalesPerson.BusinessEntityID
) 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="29825">
<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>
</SalesOrder>
<SalesOrder>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="29672">
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
</Sales.SalesOrderHeader>
</SalesOrder>
...
Следующий запрос формирует те же сведения о заказе, за исключением результирующего XML; <SalesPerson> представляет собой элемент, имеющий общего родителя с <SalesOrderDetail>:
<SalesOrder>
<SalesOrderHeader ...>
<SalesOrderDetail .../>
<SalesOrderDetail .../>
...
<SalesPerson .../>
</SalesOrderHeader>
</SalesOrder>
<SalesOrder>
...
</SalesOrder>
Запрос:
USE AdventureWorks2008R2;
GO
SELECT 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 SalesOrderID=43659 OR SalesOrderID=43660
FOR XML AUTO, TYPE;
Результат:
<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="29825">
<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>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="29672">
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
</Sales.SalesOrderHeader>
Директива TYPE возвращает результат запроса как тип данных xml, поэтому можно выполнять запросы к результирующему XML с использованием различных методов типа данных xml. Дополнительные сведения см. в разделе Методы типа данных xml. В приведенном далее запросе обратите внимание на следующее.
Предшествующий запрос добавлен в предложении FROM. Результат запроса возвращается в виде таблицы. Следует обратить внимание, что добавлен псевдоним XmlCol.
Предложение SELECT определяет запрос XQuery к XmlCol, возвращаемому в предложении FROM. Для указания запроса XQuery применяется метод query() типа данных xml. Дополнительные сведения см. в разделе 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) FROM Sales.SalesOrderHeader WHERE SalesOrderID='43659' or SalesOrderID='43660' FOR XML AUTO, TYPE ) as T(XmlCol);