Partilhar via

Gerar irmãos com uma consulta aninhada no modo automático

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

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

Exemplo

Esta consulta constrói XML que fornece informações de ordem de venda. Isto inclui o seguinte:

  • Informações de cabeçalho da ordem de venda, SalesOrderID, SalesPersonIDe OrderDate. AdventureWorks2025 armazena essas informações na tabela SalesOrderHeader.

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

  • Informações do vendedor. Este é o vendedor que recebeu o pedido. A tabela SalesPerson fornece o SalesPersonID. Para esta consulta, tem de juntar esta tabela à tabela Employee para encontrar o nome do vendedor.

As duas consultas SELECT distintas que se seguem 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 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 de XML .

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

    • A instrução SELECT que recupera SalesOrderID, SalesPersonIDe CustomerID em si inclui outra instrução SELECT ... FOR XML aninhada (com modo AUTO e diretiva TYPE) que retorna informações detalhadas da ordem do cliente.

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, deve-se fornecer um nome para o conjunto anónimo de linhas gerado na cláusula FROM. Neste 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 ordem de venda, 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 diretiva TYPE retorna um resultado de consulta como tipo de xml, pode-se consultar o XML resultante usando vários métodos de tipo de dados xml. Para obter mais informações, consulte xml Data Type Methods. 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 XmlCol que é adicionado.

  • A cláusula SELECT especifica um XQuery contra o XmlCol retornado 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 query() (xml Data Type).

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

Ver também

  • Last updated on