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

Esempio

Questa query costruisce un valore XML che contiene informazioni sugli ordini di vendita, incluse le seguenti:

• Informazioni contenute nell'intestazione degli ordini di vendita, ovvero SalesOrderID, SalesPersonID e OrderDate. Nel database AdventureWorks tali informazioni sono archiviate 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 SalesPersonID è archiviato nella tabella SalesPerson. 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. La modalità AUTO converte in formato XML il risultato della query 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, SalesPersonID e 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>

La query è riportata di seguito:

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 utilizzando vari metodi con tipo di dati xml. Per ulteriori informazioni, vedere l'argomento relativo ai metodi per i tipi 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 ulteriori informazioni, vedere Metodo query() con 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)
  

Vedere anche

Riferimento