Condividi tramite

Generare fratelli con una query annidata in modalità AUTO

Nell'esempio seguente, viene mostrato come generare fratelli utilizzando 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 XML che fornisce 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, Questo è il venditore che ha preso l'ordine. La tabella SalesPerson fornisce il SalesPersonID. Per questa query è necessario unire questa 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 codice XML in cui <SalesPerson> e <SalesOrderHeader> appaiono come elementi figlio di pari livello 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. Il risultato è un codice XML con uno o più <SalesOrder> elementi.

  • Specifica la modalità AUTO e la direttiva TYPE . AUTO mode 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 dell'ordine di vendita, l'intestazione e i dettagli, e la seconda istruzione nidificata SELECT recupera le informazioni sul 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 sugli ordini di vendita, con l'unica differenza che nel XML risultante, <SalesPerson> compare come elemento pari livello di <SalesOrderDetail>.

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

Questa è la 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 restituisce un risultato della TYPE query come xml tipo, è possibile eseguire una query sul codice XML risultante usando vari xml metodi di tipo di dati. 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 . Il query() metodo del xml tipo di dati viene utilizzato per specificare XQuery. 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

  • Last updated on