Generar elementos del mismo nivel con 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.
Ejemplo
Esta consulta genera XML que proporciona información de pedidos de ventas. Incluye lo siguiente:
Información de encabezado de pedidos de ventas, SalesOrderID, SalesPersonID y OrderDate. AdventureWorks2008R2 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.
Las dos consultas distintas siguientes generan XML con una pequeña diferencia en la forma.
La primera consulta genera XML en el que <SalesOrderHeader> aparece como un elemento secundario relacionado de <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;
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 (con el modo AUTO y la directiva TYPE ) que devuelve información de detalle de los pedidos de ventas.
La instrucción SELECT que recupera la 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.
Este es el resultado parcial:
<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>
...
La siguiente consulta genera la misma información de pedidos de ventas, pero en el XML resultante <SalesPerson> aparece como relacionado de <SalesOrderDetail>:
<SalesOrder>
<SalesOrderHeader ...>
<SalesOrderDetail .../>
<SalesOrderDetail .../>
...
<SalesPerson .../>
</SalesOrderHeader>
</SalesOrder>
<SalesOrder>
...
</SalesOrder>
Esta es la consulta:
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;
Este es el resultado:
<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>
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) FROM Sales.SalesOrderHeader WHERE SalesOrderID='43659' or SalesOrderID='43660' FOR XML AUTO, TYPE ) as T(XmlCol);