Вложенные запросы FOR XML

В SQL Server 2000 предложение FOR XML можно указать только на верхнем уровне запроса SELECT. Результирующий XML в основном возвращается клиенту для дальнейшей обработки. В SQL Server 2005 с появлением типа данных xml и директивы TYPE в запросах FOR XML текст в формате XML, возвращаемый запросами FOR XML, может быть дополнительно обработан на сервере.

  • Результат запроса FOR XML можно присвоить переменной типа xml или воспользоваться языком XQuery, чтобы выполнить к нему запрос, после чего и присвоить полученный результат переменной типа xml для дополнительной обработки.

    DECLARE @x xml
SET @x=(SELECT ProductModelID, Name
        FROM Production.ProductModel
        WHERE ProductModelID=122 or ProductModelID=119
        FOR XML RAW, TYPE)
SELECT @x
-- Result
--<row ProductModelID="122" Name="All-Purpose Bike Stand" />
--<row ProductModelID="119" Name="Bike Wash" />

    XML-данные, возвращаемые в переменной @x, можно дополнительно обработать с применением методов типа данных xml. Например, можно получить значение атрибута ProductModelID с помощью метода value() (тип данных xml).

    DECLARE @i int
SET @i = (SELECT @x.value('/row[1]/@ProductModelID[1]', 'int'))
SELECT @i

    В следующем примере результат запроса FOR XML возвращается в виде типа данных xml, так как в выражении FOR XML указана директива TYPE.

    SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=119 or ProductModelID=122
FOR XML RAW, TYPE,ROOT('myRoot')

    Результат:

    <myRoot>
  <row ProductModelID="122" Name="All-Purpose Bike Stand" />
  <row ProductModelID="119" Name="Bike Wash" />
</myRoot>

    Поскольку результат имеет тип xml, непосредственно к нему можно применить один из методов типа данных xml, как показано в следующем запросе. В запросе метод query() (тип данных xml) используется для получения первого дочернего элемента <row> элемента <myRoot>.

    SELECT  (SELECT ProductModelID, Name
         FROM Production.ProductModel
         WHERE ProductModelID=119 or ProductModelID=122
         FOR XML RAW, TYPE,ROOT('myRoot')).query('/myRoot[1]/row[1]')

    Результат:

    <row ProductModelID="122" Name="All-Purpose Bike Stand" />

  • Кроме этого, можно создавать вложенные запросы FOR XML, где результат внутреннего запроса возвращает внешнему запросу данные типа xml. Например:

    SELECT Col1, 
       Col2, 
       ( SELECT Col3, Col4 
        FROM  T2
        WHERE T2.Col = T1.Col
        ...
        FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE

    В предыдущем запросе обратите внимание на следующие моменты:

    • XML-данные, сформированные внутренним запросом FOR XML, добавляются к XML-результату внешнего запроса FOR XML.
    • Во внутреннем запросе указана директива TYPE. Таким образом, возвращаемые внутренним запросом XML-данные имеют тип xml. Если директива TYPE не указана, внутренний запрос FOR XML возвращает результат типа nvarchar(max) и XML-данные преобразуются в сущности.

    Вложенные запросы FOR XML дают больший контроль над форматом результирующих XML-данных.

    • В SQL Server 2000 запросы режимов RAW и AUTO по умолчанию формируют атрибутивную модель XML-данных. Например:

      SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW

      Результат является атрибутивным:

      <row ProductModelID="122" Name="All-Purpose Bike Stand" />
<row ProductModelID="119" Name="Bike Wash" />

      Указав директиву ELEMENTS, можно получить все XML-данные в элементной форме. Например:

      SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW, ELEMENTS

      Результат является элементным:

      <row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>

      SQL Server 2005 позволяет для вложенных запросов FOR XML конструировать XML-данные, сочетающие в себе атрибутивную и элементную модели.

    • В SQL Server 2000 узлы с общим родителем можно создавать только с помощью запросов в режиме EXPLICIT. Однако пользоваться этим способом не всегда удобно. В SQL Server 2005 с помощью вложенных запросов FOR XML в режиме AUTO можно формировать XML-иерархии, содержащие узлы с общим родителем.

    Независимо от используемого режима вложенные запросы FOR XML предоставляют больший контроль при описании формата результирующих XML-данных. Их можно использовать вместо запросов в режиме EXPLICIT.

Примеры

А. Сравнение запроса FOR XML с вложенным запросом FOR XML

Следующий запрос SELECT позволяет получить сведения о категории и подкатегории продукта в базе данных AdventureWorks. В запросе нет вложенных запросов FOR XML.

SELECT   ProductCategory.ProductCategoryID, 
         ProductCategory.Name as CategoryName,
         ProductSubCategory.ProductSubCategoryID, 
         ProductSubCategory.Name
FROM     Production.ProductCategory, Production.ProductSubCategory
WHERE    ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryID
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE
GO

Промежуточный результат:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory ProductSubCategoryID="1" Name="Mountain Bike"/>
  <ProductSubCategory ProductSubCategoryID="2" Name="Road Bike"/>
  <ProductSubCategory ProductSubCategoryID="3" Name="Touring Bike"/>
</ProductCategory>
...

Если в запросе указана директива ELEMENTS, то результат будет представлен в элементной форме, как показано в следующем фрагменте результата:

<ProductCategory>
  <ProductCategoryID>1</ProductCategoryID>
  <CategoryName>Bike</CategoryName>
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <Name>Mountain Bike</Name>
  </ProductSubCategory>
  <ProductSubCategory>
     ...
  </ProductSubCategory>
</ProductCategory>

Предположим, что нужно создать иерархию XML-данных, которая сочетает атрибутивную и элементную модели, как показано в следующем фрагменте:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

В этом фрагменте такие сведения о категории продукта, как идентификатор категории и имя категории, являются атрибутами. Однако сведения о подкатегории представлены с использованием элементов. Для конструирования элемента <ProductCategory> можно написать запрос FOR XML, как показано ниже:

SELECT ProductCategoryID, Name as CategoryName
FROM Production.ProductCategory ProdCat
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

Результат:

< ProdCat ProductCategoryID="1" CategoryName="Bikes" />
< ProdCat ProductCategoryID="2" CategoryName="Components" />
< ProdCat ProductCategoryID="3" CategoryName="Clothing" />
< ProdCat ProductCategoryID="4" CategoryName="Accessories" />

Для создания необходимых вложенных элементов <ProductSubCategory> необходимо после этого добавить вложенный запрос FOR XML, как показано ниже:

SELECT ProductCategoryID, Name as CategoryName,
       (SELECT ProductSubCategoryID, Name SubCategoryName
        FROM   Production.ProductSubCategory
        WHERE ProductSubCategory.ProductCategoryID = 
              ProductCategory.ProductCategoryID
        FOR XML AUTO, TYPE, ELEMENTS
       )
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

Для предыдущего запроса необходимо отметить следующее:

  • Внутренний запрос FOR XML получает сведения о подкатегории продукта. Директива ELEMENTS добавляется во внутренний запрос FOR XML, создавая элементный XML, который добавляется к XML-данным, создаваемым внешним запросом. По умолчанию внешний запрос создает XML-данные по атрибутивной модели.
  • Во внутреннем запросе указана директива TYPE, поэтому результат имеет тип xml. Если директива TYPE не указана, то возвращается результат типа nvarchar(max) и XML-данные возвращаются как сущности.
  • Внешний запрос также содержит директиву TYPE, поэтому результат запроса возвращается клиенту как тип xml.

Промежуточный результат:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

Следующий запрос представляет собой расширение предыдущего запроса. Он показывает полную иерархию продуктов в базе данных AdventureWorks. Это включает следующее.

  • Категории продукции
  • Подкатегории продукции в каждой категории
  • Модели продуктов в каждой подкатегории
  • Продукты в каждой модели

Следующий пример может быть полезен для понимания базы данных AdventureWorks:

SELECT ProductCategoryID, Name as CategoryName,
       (SELECT ProductSubCategoryID, Name SubCategoryName,
               (SELECT ProductModel.ProductModelID, 
                       ProductModel.Name as ModelName,
                       (SELECT ProductID, Name as ProductName, Color
                        FROM   Production.Product
                        WHERE  Product.ProductModelID = 
                               ProductModel.ProductModelID
                        FOR XML AUTO, TYPE)
                FROM   (SELECT distinct ProductModel.ProductModelID, 
                               ProductModel.Name
                        FROM   Production.ProductModel, 
                               Production.Product
                        WHERE  ProductModel.ProductModelID = 
                               Product.ProductModelID
                        AND    Product.ProductSubCategoryID = 
                               ProductSubCategory.ProductSubCategoryID) 
                                  ProductModel
                FOR XML AUTO, type
               )
        FROM Production.ProductSubCategory
        WHERE ProductSubCategory.ProductCategoryID = 
              ProductCategory.ProductCategoryID
        FOR XML AUTO, TYPE, ELEMENTS
       )
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

Промежуточный результат:

<Production.ProductCategory ProductCategoryID="1" CategoryName="Bikes">
  <Production.ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bikes</SubCategoryName>
    <ProductModel ProductModelID="19" ModelName="Mountain-100">
      <Production.Product ProductID="771" 
                ProductName="Mountain-100 Silver, 38" Color="Silver" />
      <Production.Product ProductID="772" 
                ProductName="Mountain-100 Silver, 42" Color="Silver" />
      <Production.Product ProductID="773" 
                ProductName="Mountain-100 Silver, 44" Color="Silver" />
        …
    </ProductModel>
     …

Если удалить директиву ELEMENTS из вложенного запроса FOR XML, который создает подкатегории продуктов, то весь результат будет создан по атрибутивной модели. Этот запрос можно составить без вложений. Добавление директивы ELEMENTS приводит к созданию XML-документа как по атрибутивной, так и по элементной модели. Этот результат нельзя сформировать с помощью одноуровневого запроса FOR XML.

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

В следующем примере показано, как создавать элементы с общим родителем при помощи вложенного запроса в режиме AUTO. Единственный способ создать такой XML — использовать режим EXPLICIT. Однако воспользоваться этим способом не всегда удобно.

Этот запрос создает XML, который предоставляет сведения о заказах на продажу. Это включает следующее:

  • В базе данных AdventureWorks заголовки данных о заказах продаж SalesOrderID, SalesPersonID и OrderDate хранятся в таблице 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>
...

Следующий запрос формирует те же сведения о заказе, за исключением результирующего 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 определяет запрос 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),
              (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)

В. Создание приложения ASPX для получения сведений о заказе на продажу в обозревателе

В этом примере ASPX-приложение выполняет хранимую процедуру и возвращает сведения о заказе на продажу в формате XML. Результат отображается в обозревателе. Инструкция SELECT в хранимой процедуре похожа на инструкцию, приведенную в примере Б, за исключением того, что результирующий XML является элементным.

CREATE PROC GetSalesOrderInfo AS
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, ELEMENTS)
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
GO

Это приложение ASPX. Оно выполняет хранимую процедуру и возвращает XML в обозреватель:

<%@LANGUAGE=C# Debug=true %>
<%@import Namespace="System.Xml"%>
<%@import namespace="System.Data.SqlClient" %><%
Response.Expires = -1;
Response.ContentType = "text/xml";
%>

<%
using(System.Data.SqlClient.SqlConnection c = new System.Data.SqlClient.SqlConnection("Data Source=server;Database=AdventureWorks;Integrated Security=SSPI;"))
using(System.Data.SqlClient.SqlCommand cmd = c.CreateCommand())
{
   cmd.CommandText = "GetSalesOrderInfo";
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Connection.Open();
   System.Xml.XmlReader r = cmd.ExecuteXmlReader();
   System.Xml.XmlTextWriter w = new System.Xml.XmlTextWriter(Response.Output);
   w.WriteStartElement("Root");
   r.MoveToContent();
   while(! r.EOF)
   {
      w.WriteNode(r, true);
   }
   w.WriteEndElement();
   w.Flush();
}
%>
Тестирование приложения
  1. Создайте хранимую процедуру в базе данных AdventureWorks.
  2. Сохраните приложение .aspx в каталоге c:\inetpub\wwwroot (GetSalesOrderInfo.aspx).
  3. Выполните приложение (https://server/GetSalesOrderInfo.aspx).

Г. Построение XML, содержащего цены продуктов

В следующем примере к таблице Production.Product выполняется запрос, чтобы получить значения ListPrice и StandardCost указанного продукта. Чтобы сделать пример более информативным, обе цены возвращаются как элемент <Price> и у каждого элемента <Price> имеется атрибут PriceType. Это прогнозируемая форма XML:

<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" elementFormDefault="qualified">
  <xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="Production.Product" type="xsd:anyType" />
</xsd:schema>
<Production.Product xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" ProductID="520">
  <Price  PriceType="ListPrice">133.34</Price>
  <Price  PriceType="StandardCost">98.77</Price>
</Production.Product>

Это вложенный запрос FOR XML:

SELECT Product.ProductID, 
          (SELECT 'ListPrice' as PriceType, 
                   CAST(CAST(ListPrice as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE),
          (SELECT  'StandardCost' as PriceType, 
                   CAST(CAST(StandardCost as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE)
FROM Production.Product
WHERE ProductID=520
for XML AUTO, TYPE, XMLSCHEMA

Из предыдущего запроса стоит отметить следующее.

  • Внешняя инструкция SELECT строит элемент <Product>, который имеет атрибут ProductID и два дочерних элемента <Price>.
  • Две внутренние инструкции SELECT создают два элемента <Price>, каждый с атрибутом PriceType, а также XML, который возвращает цену продукта.
  • Директива XMLSCHEMA во внешней инструкции SELECT создает встроенную XSD-схему, которая описывает форму результирующего XML.

Чтобы сделать запрос более информативным, можно составить запрос FOR XML, а затем подготовить запрос XQuery к результату для переформирования XML, как показано в следующем запросе:

SELECT ProductID, 
 ( SELECT p2.ListPrice, p2.StandardCost
   FROM Production.Product p2 
   WHERE Product.ProductID = p2.ProductID
   FOR XML AUTO, ELEMENTS XSINIL, type ).query('
                                   for $p in /p2/*
                                   return 
                                    <Price PriceType = "{local-name($p)}">
                                     { data($p) }
                                    </Price>
                                  ')
FROM Production.Product
WHERE ProductID = 520
FOR XML AUTO, TYPE

В предыдущем примере метод query() типа данных xml используется, чтобы запросить XML, возвращаемый внутренним запросом FOR XML, и построить прогнозируемый результат.

Результат:

<Production.Product ProductID="520">
  <Price PriceType="ListPrice">133.3400</Price>
  <Price PriceType="StandardCost">98.7700</Price>
</Production.Product>
  • Last updated on