Compartir a través de

Consultas FOR XML anidadas

  • Artículo

En SQL Server 2000, puede especificar la cláusula FOR XML sólo en el nivel superior de una consulta SELECT. El XML resultante se devuelve principalmente al cliente para el procesamiento adicional. En SQL Server 2005, con la incorporación del tipo de datos xml y la directiva TYPE en consultas FOR XML, el XML devuelto por las consultas FOR XML se puede procesar de manera adicional en el servidor.

  • Puede asignar el resultado de la consulta FOR XML a una variable de tipo xml, o utilizar XQuery para consultar el resultado y asignar ese resultado a una variable de tipo xml para procesarlo más.

    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" />

    También puede procesar el XML devuelto en la variable, @x, utilizando alguno de los métodos de tipo de datos xml. Por ejemplo, puede recuperar el valor del atributo ProductModelID utilizando value() (método del tipo de datos xml).

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

    En el siguiente ejemplo, el resultado de la consulta FOR XML se devuelve como tipo xml porque se ha especificado la directiva TYPE en la cláusula FOR XML.

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

    El resultado es el siguiente:

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

    Puesto que el resultado es de tipo xml, puede especificar uno de los métodos de tipo de datos xml directamente para este XML, como se muestra en la siguiente consulta. En la consulta, se utiliza el método query() del tipo de datos xml para recuperar el primer elemento secundario <row> del elemento <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]')

    El resultado es el siguiente:

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

  • Además, puede escribir consultas FOR XML anidadas en las que el resultado de la consulta interna se devuelve como tipo xml a la consulta externa. Por ejemplo:

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

    Observe lo siguiente en la consulta anterior:

    • El XML generado por la consulta FOR XML interna se agrega al XML generado por la consulta FOR XML externa.
    • En la siguiente consulta interna se especifica la directiva TYPE. Por lo tanto, los datos XML devueltos por la consulta interna son de tipo xml. Si no se especifica la directiva TYPE, el resultado de la consulta FOR XML interna se devuelve como nvarchar(max) y se crean entidades de los datos XML.

    Las consultas FOR XML anidadas ofrecen mayor control en la definición de la forma de los datos XML resultantes.

    • En SQL Server 2000, las consultas en modo RAW y AUTO generan XML centrado en atributos de forma predeterminada. Por ejemplo:

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

      El resultado centrado en atributos es el siguiente:

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

      Si especifica la directiva ELEMENTS, puede recuperar todo el XML centrado en elementos. Por ejemplo:

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

      El resultado centrado en elementos es el siguiente:

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

      Para las consultas FOR XML anidadas en SQL Server 2005, puede construir XML que esté parcialmente centrado en atributos y parcialmente centrado en elementos.

    • En SQL Server 2000, sólo puede construir elementos del mismo nivel escribiendo consultas que utilicen el modo EXPLICIT. Sin embargo, esto puede ser tedioso. En SQL Server 2005, puede generar jerarquías XML que incluyen elementos del mismo nivel si se especifican consultas FOR XML en modo AUTO anidadas.

    Con independencia del modo que se utilice, las consultas FOR XML anidadas proporcionan mayor control en la descripción de la forma del XML resultante. Se pueden usar en lugar de las consultas en modo EXPLICIT.

Ejemplos

A. Comparar una consulta FOR XML con una consulta FOR XML anidada

La siguiente consulta SELECT recupera información de categoría y subcategoría de productos de la base de datos AdventureWorks. No hay FOR XML anidado en la consulta.

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

Éste es el resultado parcial:

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

Si especifica la directiva ELEMENTS en la consulta, recibe un resultado centrado en elementos, como se muestra en el siguiente fragmento de resultados:

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

A continuación, imagine que desea generar una jerarquía XML que sea una combinación de XML centrado en atributos y centrado en elementos, como se muestra en el siguiente fragmento:

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

En el fragmento anterior, la información de categoría de productos, como el Id. y el nombre de categoría, son atributos. Sin embargo, la información de subcategoría está centrada en elementos. Para construir el elemento <ProductCategory>, puede escribir una consulta FOR XML como se muestra a continuación:

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

El resultado es el siguiente:

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

Para construir los elementos <ProductSubCategory> anidados en el XML que desee, debe agregar una consulta FOR XML anidada, como se muestra a continuación:

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

Observe lo siguiente en la consulta anterior:

  • La consulta FOR XML interna recupera información de subcategoría de productos. La directiva ELEMENTS se agrega a la consulta FOR XML interna para generar XML centrado en elementos que se agrega al XML generado por la consulta externa. De manera predeterminada, la consulta externa genera XML centrado en atributos.
  • En la consulta interna, se especifica la directiva TYPE para que el resultado sea de tipo xml. Si no se especifica TYPE, el resultado se devuelve como tipo nvarchar(max) y los datos XML se devuelven como entidades.
  • La consulta externa especifica también la directiva TYPE. Por tanto, el resultado de esta consulta se devuelve al cliente como tipo xml.

Éste es el resultado parcial:

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

La consulta siguiente es sólo una extensión de la anterior. Muestra la jerarquía de productos completa en la base de datos AdventureWorks. Incluye lo siguiente:

  • Categorías de productos
  • Subcategorías de productos de cada categoría
  • Modelos de productos en cada subcategoría
  • Productos en cada modelo

Quizá encuentre útil la siguiente consulta para comprender la base de datos 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

Éste es el resultado parcial:

<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>
     …

Si quita la directiva ELEMENTS de la consulta FOR XML anidada que genera subcategorías de productos, todo el resultado está centrado en atributos. Después puede escribir esta consulta sin anidar. La adición de ELEMENTS da lugar a un XML parcialmente centrado en atributos y parcialmente centrado en elementos. Este resultado no se puede generar en un solo nivel, consulta FOR XML.

B. Generar elementos del mismo nivel utilizando una consulta en modo AUTO anidada

En el siguiente ejemplo se muestra cómo generar elementos del mismo nivel utilizando una consulta en modo AUTO anidada. Sólo hay otra forma de generar este XML, que es utilizar el modo EXPLICIT. Sin embargo, esto puede ser tedioso.

Esta consulta genera XML que proporciona información de pedidos de ventas. Incluye lo siguiente:

  • Información de encabezado de pedidos de venta, SalesOrderID, SalesPersonID y OrderDate. AdventureWorks almacena esta información en la tabla SalesOrderHeader.
  • Información detallada de pedidos de ventas. Esto incluye uno o varios productos pedidos, el precio por unidad y la cantidad pedida. Esta información se almacena en la tabla SalesOrderDetail.
  • Información del vendedor. Es el vendedor que tomó el pedido. En la tabla SalesPerson se proporciona el valor SalesPersonID. Para esta consulta, tiene que combinar esta tabla con la tabla Employee para buscar el nombre del vendedor.

Las dos consultas SELECT siguientes generan XML con una pequeña diferencia en la forma.

La primera consulta genera XML en el que <SalesPerson> y <SalesOrderHeader> aparecen como elementos secundarios iguales de <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

En la consulta anterior, la instrucción SELECT más externa realiza las siguientes acciones:

  • Consulta el conjunto de filas, SalesOrder, especificado en la cláusula FROM. El resultado es un XML con uno o varios elementos <SalesOrder>.
  • Especifica el modo AUTO y la directiva TYPE. El modo AUTO transforma el resultado de la consulta en XML y la directiva TYPE devuelve el resultado como tipo xml.
  • Incluye dos instrucciones SELECT anidadas separadas por una coma. La primera instrucción SELECT anidada recupera información de pedidos de ventas, encabezado y detalles, y la segunda instrucción SELECT anidada recupera información del vendedor.
    • La instrucción SELECT que recupera SalesOrderID, SalesPersonID y CustomerID incluye otra instrucción SELECT ... FOR XML anidada (con el modo AUTO y la directiva TYPE) que devuelve información de detalles de pedidos de venta.

La instrucción SELECT que recupera información del vendedor consulta un conjunto de filas, SalesPerson, creado en la cláusula FROM. Para que las consultas FOR XML funcionen, debe proporcionar un nombre para el conjunto de filas anónimo generado en la cláusula FROM. En este caso, el nombre proporcionado es SalesPerson.

Éste es el resultado parcial:

<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>
...

La siguiente consulta genera la misma información de pedidos de ventas, pero en el XML resultante <SalesPerson> aparece como igual de <SalesOrderDetail>:

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

Ésta es la consulta:

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

El resultado es el siguiente:

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

Puesto que la directiva TYPE devuelve un resultado de consulta de tipo xml, puede consultar el XML resultante utilizando varios métodos del tipo de datos xml. Para obtener más información, vea Métodos de tipo de datos xml. En la siguiente consulta, tenga en cuenta lo siguiente:

  • La consulta anterior se agrega a la cláusula FROM. El resultado de la consulta se devuelve como una tabla. Observe el alias XmlCol agregado.

  • La cláusula SELECT especifica una consulta XQuery en el XmlCol devuelto en la cláusula FROM. Para especificar la consulta XQuery se utiliza el método query() del tipo de datos xml. Para obtener más información, vea query() (método de tipo de datos 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)

C. Crear una aplicación ASPX para recuperar información de pedidos de ventas en el explorador

En el siguiente ejemplo, una aplicación aspx ejecuta un procedimiento almacenado y devuelve información de pedidos de ventas como XML. El resultado se muestra en el explorador. La instrucción SELECT del procedimiento almacenado es similar a la del ejemplo B, pero el XML resultante está centrado en elementos.

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

Ésta es la aplicación .aspx. Ejecuta el procedimiento almacenado y devuelve el XML en el explorador:

<%@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();
}
%>
Para probar la aplicación
  1. Cree el procedimiento almacenado en la base de datos AdventureWorks.
  2. Guarde la aplicación .aspx en el directorio c:\inetpub\wwwroot (GetSalesOrderInfo.aspx).
  3. Ejecute la aplicación (https://server/GetSalesOrderInfo.aspx).

D. Generar XML que incluya precios de productos

El ejemplo siguiente consulta la tabla Production.Product para recuperar los valores ListPrice y StandardCost de un producto específico. Para hacer interesante la consulta, se devuelven ambos precios en un elemento <Price>, y cada elemento <Price> tiene un atributo PriceType. Ésta es la forma esperada del 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>

Ésta es la consulta FOR XML anidada:

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

Observe lo siguiente en la consulta anterior:

  • La instrucción SELECT externa construye el elemento <Product> que tiene un atributo ProductID y dos secundarios <Price>.
  • Las dos instrucciones SELECT internas construyen dos elementos <Price>, cada uno con un atributo PriceType y XML que devuelve el precio del producto.
  • La directiva XMLSCHEMA de la instrucción SELECT externa genera el esquema XSD en línea que describe la forma del XML resultante.

Para hacer interesante la consulta, puede escribir la consulta FOR XML y después escribir una consulta XQuery para el resultado con el fin de cambiar la forma del XML, como se muestra en la siguiente consulta:

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

En el ejemplo anterior se utiliza el método query() del tipo de datos xml para consultar el XML devuelto por la consulta FOR XML interna y construir el resultado esperado.

El resultado es el siguiente:

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