Compartilhar via

Gerar irmãos com uma consulta aninhada em modo AUTO

O exemplo a seguir mostra como gerar irmãos usando uma consulta aninhada do modo AUTO. A única outra maneira de gerar esse XML é usar o modo EXPLICIT. No entanto, isso pode ser complicado.

Exemplo

Essa consulta constrói XML que fornece informações sobre pedidos de vendas. Isso inclui o seguinte:

  • Informações do cabeçalho do pedido de vendas, SalesOrderID, SalesPersonID e OrderDate. AdventureWorks2012 armazena essas informações na SalesOrderHeader tabela.

  • Informações detalhadas do pedido de venda. Isso inclui um ou mais produtos pedidos, o preço unitário e a quantidade ordenada. Essas informações são armazenadas na SalesOrderDetail tabela.

  • Informações de vendedor. Este é o vendedor que fez o pedido. A tabela SalesPerson fornece o SalesPersonID. Para essa consulta, você precisa unir essa tabela à Employee tabela para encontrar o nome do vendedor.

As duas consultas distintas SELECT a seguir geram XML com uma pequena diferença na forma.

A primeira consulta gera XML no qual <SalesPerson> e>SalesOrderHeader< aparecem como filhos irmãos 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

Na consulta prévia, a instrução mais externa SELECT faz o seguinte:

  • Consulta o conjunto de linhas SalesOrder, especificado na cláusula FROM. O resultado é um XML com um ou mais <SalesOrder> elementos.

  • Especifica o AUTO modo e a TYPE diretiva. AUTO O modo transforma o resultado da consulta em XML, e a TYPE diretiva retorna o resultado como tipo xml.

  • Inclui duas declarações aninhadas SELECT, separadas por uma vírgula. A primeira declaração aninhada SELECT recupera informações do pedido de vendas, incluindo cabeçalho e detalhes, e a segunda declaração aninhada SELECT recupera informações do vendedor.

    • A instrução SELECT que recupera SalesOrderID, SalesPersonID e CustomerID por si só inclui outra instrução aninhada SELECT ... FOR XML (com modo AUTO e diretiva TYPE) que retorna informações detalhadas do pedido de vendas.

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, você deve fornecer 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="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>  
...

A consulta a seguir gera as mesmas informações de pedido de vendas, exceto que no XML resultante, o <SalesPerson> aparece como um irmão de <SalesOrderDetail>:

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

Esta é a 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

Este é o resultado:

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

Como a TYPE diretiva retorna um resultado de consulta como xml tipo, você pode consultar o XML resultante usando vários xml métodos de tipo de dados. Para obter mais informações, consulte métodos de tipo de dados xml. Na consulta a seguir, observe o seguinte:

  • A consulta anterior é adicionada à FROM cláusula. O resultado da consulta é retornado como uma tabela. Observe o alias XmlCol adicionado.

  • A SELECT cláusula especifica um XQuery em relação ao XmlCol retornado na FROM cláusula. O query() método do xml tipo de dados é usado na especificação do XQuery. Para obter mais informações, consulte o método query() (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),  
              (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)

Consulte Também

Usar consultas FOR XML aninhadas

  • Last updated on