Condividi tramite

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

  • Articolo

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, Sono inclusi gli elementi seguenti:

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

  • 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 xml in cui <>SalesPersonvengono <>SalesOrderHeadervisualizzati come elementi figlio <SalesOrder>di :

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. Il risultato è un XML con uno o più <SalesOrder> elementi.

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

  • 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 sull'ordine di vendita, ad eccezione del fatto che nel codice XML risultante, viene <SalesPerson> visualizzato come pari <SalesOrderDetail>a :

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

Vedere anche

Utilizzo di query FOR XML nidificate