Gerando irmãos usando uma consulta aninhada em modo AUTO
O exemplo a seguir mostra como gerar irmãos usando uma consulta aninhada em modo AUTO. A única outra maneira de gerar esse tipo de XML é usar o modo EXPLICIT. No entanto isso pode ser trabalhoso.
Exemplo
Esta consulta constrói XML que fornece informações de pedidos de vendas. Isso inclui o seguinte:
Informações do cabeçalho de pedidos de vendas, SalesOrderID, SalesPersonID e OrderDate. O AdventureWorks2008R2 armazena essas informações na tabela SalesOrderHeader.
Informações sobre detalhes de pedidos de vendas. Isso inclui um ou mais produtos pedidos, o preço unitário e a quantidade pedida. Essas informações são armazenadas na tabela SalesOrderDetail.
Informações sobre o vendedor. Esse é o vendedor que obteve o pedido.
As duas consultas distintas a seguir geram XML com uma pequena diferença na forma.
A primeira consulta gera XML no qual <SalesOrderHeader> aparece como um irmão 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;
Na consulta anterior, a instrução SELECT mais externa faz o seguinte:
Consulta o conjunto de linhas, SalesOrder, especificado na cláusula FROM. O resultado é um XML com um ou mais elementos <SalesOrder>.
Especifica o modo AUTO e a diretiva TYPE. O modo AUTO transforma o resultado da consulta em XML, e a diretiva TYPE retorna o resultado como tipo xml.
Inclui duas instruções SELECT aninhadas separadas por uma vírgula. A primeira SELECT aninhada recupera informações sobre pedidos de vendas, cabeçalho e detalhes, e a segunda SELECT aninhada recupera informações sobre o vendedor.
- A própria instrução SELECT que recupera SalesOrderID, SalesPersonID e CustomerID inclui outra SELECT aninhada ... Instrução FOR XML (com modo AUTO e diretiva TYPE) que retorna informações de detalhe da ordem de venda.
A instrução SELECT que recupera as informações do vendedor consulta um conjunto de linhas, SalesPerson, criado na cláusula FROM. Para que as consultas FOR XML funcionem, forneça um nome para o conjunto de linhas anônimo gerado na cláusula FROM. Nesse caso, o nome fornecido é SalesPerson.
Este é o 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>
...
A consulta a seguir gera as mesmas informações sobre pedidos de vendas, exceto que no XML resultante, o <SalesPerson> aparece como irmão de <SalesOrderDetail>:
<SalesOrder>
<SalesOrderHeader ...>
<SalesOrderDetail .../>
<SalesOrderDetail .../>
...
<SalesPerson .../>
</SalesOrderHeader>
</SalesOrder>
<SalesOrder>
...
</SalesOrder>
Esta é a 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 é o 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>
Como a diretiva TYPE retorna um resultado de consulta como tipo xml, é possível consultar o XML resultante usando vários métodos de tipo de dados xml. Para obter mais informações, consulte Métodos de tipo de dados xml. Na consulta a seguir, observe o seguinte:
A consulta anterior é adicionada na cláusula FROM. O resultado da consulta é retornado como uma tabela. Observe o alias de XmlCol que é adicionado.
A cláusula SELECT especifica um XQuery em relação à XmlCol retornada na cláusula FROM. O método query() do tipo de dados xml é usado para especificar o XQuery. Para obter mais informações, consulte Método consulta() (Tipo de dados 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);