Generare elementi di pari livello tramite query nidificate in modalità AUTO

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

Nell'esempio seguente viene descritta la procedura per generare elementi di pari livello tramite una query nidificata in modalità AUTO. L'unico metodo alternativo per generare un valore XML di questo tipo è utilizzare la modalità EXPLICIT, ma ciò può rivelarsi eccessivamente complesso.

Esempio

Questa query costruisce un valore XML che contiene informazioni sugli ordini di vendita, È incluso quanto segue:

  • Informazioni contenute nell'intestazione degli ordini di vendita, SalesOrderID, SalesPersonIDe OrderDate. AdventureWorks2022 archivia queste informazioni nella tabella SalesOrderHeader .

  • Informazioni dettagliate sugli ordini di vendita, che includono i prodotti ordinati, il prezzo unitario e la quantità ordinata. Tali informazioni sono archiviate nella tabella SalesOrderDetail .

  • Informazioni sul venditore, ovvero la persona che ha ricevuto l'ordine. Il codice SalesPerson è archiviato nella tabella SalesPersonID. Per questa query è necessario unire in join tale tabella con la tabella Employee , per trovare il nome del venditore.

Le due query SELECT che seguono generano valori XML con struttura lievemente diversa.

La prima query genera un valore XML in cui <SalesPerson> e <SalesOrderHeader> sono elementi di pari livello figli di <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;

Nella query precedente l'istruzione SELECT più esterna esegue le operazioni seguenti:

  • Esegue una query sul set di righe SalesOrder, specificato nella clausola FROM. Viene restituito un valore XML con uno o più elementi <SalesOrder>.

  • Specifica la modalità AUTO e la direttiva TYPE . AUTO trasforma il risultato della query in XML e la direttiva TYPE restituisce il risultato come tipo xml .

  • Include due istruzioni SELECT nidificate, separate da una virgola (,). La prima istruzione nidificata SELECT recupera le informazioni relative all'ordine di vendita, l'intestazione ed i dettagli, mentre la seconda istruzione SELECT recupera le informazioni relative al venditore.

    • L'istruzione SELECT che recupera SalesOrderID, SalesPersonIDe CustomerID include a sua volta un'altra istruzione nidificata SELECT ... FOR XML (con modalità AUTO e direttiva TYPE ), che restituisce i dettagli dell'ordine di vendita.

L'istruzione SELECT che recupera le informazioni relative al venditore esegue una query sul set di righe SalesPerson, creato nella clausola FROM . Per consentire l'esecuzione delle query FOR XML , è necessario specificare un nome per il set di righe anonimo generato nella clausola FROM . In tal caso viene specificato il nome SalesPerson.

Risultato parziale:

<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 query seguente genera le stesse informazioni relative all'ordine di vendita, con la differenza che nel valore XML risultante <SalesPerson> appare un elemento di pari livello di <SalesOrderDetail>:

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

</SalesOrder>
<SalesOrder>
  ...
</SalesOrder>

Query:

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;

Risultato:

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

Poiché la direttiva TYPE restituisce il risultato della query come tipo xml , è possibile eseguire una query sul valore XML risultante usando vari metodi con tipo di dati xml . Per altre informazioni, vedere Metodi con tipo di dati XML. In questa query di esempio, si noti quanto segue:

  • La query precedente viene aggiunta nella clausola FROM . Il risultato della query viene restituito sotto forma di tabella. Viene aggiunto l'alias XmlCol .

  • La clausola SELECT specifica una query XQuery sul valore XmlCol restituito nella clausola FROM . Per specificare la query XQuery viene utilizzato il metodo query() con tipo di dati xml. Per altre informazioni, vedere Metodo query() (tipo di dati 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);

Vedi anche