Génération de frères à l'aide d'une requête imbriquée en mode AUTO

Exemple

Cette requête construit le document XML qui fournit les informations sur les commandes. Notamment :

• Les informations sur les en-têtes des commandes, SalesOrderID, SalesPersonID et OrderDate. AdventureWorks stocke ces informations dans la table SalesOrderHeader.

• Les informations sur les détails des commandes. Elles indiquent un ou plusieurs produits commandés, le prix unitaire et la quantité commandée. Ces informations sont stockées dans la table SalesOrderDetail.

• Les informations sur le vendeur. Il s'agit du vendeur qui a traité la commande. La table SalesPerson fournit le SalesPersonID. Pour cette requête, vous devez joindre cette table à la table Employee afin de rechercher le nom du vendeur.

Les deux requêtes SELECT distinctes ci-après génèrent un document XML dont la forme varie peu.

La première requête génère un document XML dans lequel <SalesPerson> et <SalesOrderHeader> apparaissent en tant qu'enfants frères 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

Dans la requête précédente, l'instruction SELECT la plus externe effectue les opérations suivantes :

• Elle interroge l'ensemble de lignes SalesOrder, spécifié dans la clause FROM. Le résultat est un document XML possédant un ou plusieurs éléments <SalesOrder>.

• Elle spécifie le mode AUTO et la directive TYPE. Le mode AUTO transforme le résultat de la requête en tant que XML et le directive TYPE retourne le résultat en tant que type xml .

• Elle inclut deux instructions SELECT imbriquées séparées par une virgule. La première instruction SELECT imbriquée extrait les informations sur la commande, l'en-tête et les détails, tandis que la seconde instruction SELECT imbriquée extrait les informations sur le vendeur.

  • L'instruction SELECT qui extrait SalesOrderID, SalesPersonID et CustomerID inclut elle-même une autre instruction imbriquée SELECT ... FOR XML (avec le mode AUTO et la directive TYPE) qui retourne des informations sur les détails des commandes.

L'instruction SELECT qui extrait les informations sur le vendeur interroge un ensemble de lignes, SalesPerson, créé dans la clause FROM. Pour que les requêtes FOR XML fonctionnent, vous devez fournir un nom pour l'ensemble de lignes anonyme généré dans la clause FROM. Dans ce cas, le nom fourni est SalesPerson.

Voici le résultat partiel :

<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 requête suivante génère les mêmes informations sur la commande, sauf que, dans le document XML obtenu, <SalesPerson> apparaît en tant que frère de <SalesOrderDetail> :

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

Voici la requête :

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

Voici le résultat obtenu :

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

Étant donné que la directive TYPE renvoie un résultat de requête de type xml, vous pouvez recourir à différentes méthodes de type de données xml pour interroger le document XML obtenu. Pour plus d'informations, consultez Méthodes de données de type xml. Dans la requête suivante, notez ce qui suit :

• La requête précédente est ajoutée à la clause FROM. Les résultats de la requête sont renvoyés sous la forme d'une table. Notez l'ajout de l'alias XmlCol.

• La clause SELECT définit une requête XQuery par rapport à l'alias XmlCol renvoyé dans la clause FROM. La méthode query() du type de données xml est utilisée dans la spécification de la requête XQuery. Pour plus d'informations, consultez Méthode query() (type de données 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)
  

Voir aussi

Référence