Partager via

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

L’exemple suivant montre comment générer des frères à l’aide d’une requête en mode AUTO imbriquée. La seule autre façon de générer de telles données XML consiste à utiliser le mode EXPLICIT. Toutefois, cela peut être fastidieux.

Exemple :

Cette requête construit du code XML qui fournit des informations sur les commandes. Notamment :

  • Informations d’en-tête de commande client, SalesOrderID, SalesPersonIDet OrderDate. AdventureWorks2012 stocke ces informations dans la SalesOrderHeader table.

  • Informations détaillées sur les commandes commerciales. Cela inclut un ou plusieurs produits commandés, le prix unitaire et la quantité ordonnée. Ces informations sont stockées dans la SalesOrderDetail table.

  • Informations de la personne de vente. C’est l’vendeur qui a pris la commande. La table SalesPerson fournit le SalesPersonID. Pour cette requête, vous devez joindre ce tableau à la table Employee pour trouver le nom du commercial.

Les deux requêtes distinctes SELECT qui suivent génèrent du code XML avec une petite différence de forme.

La première requête génère du code XML dans lequel <SalesPerson> il>SalesOrderHeader< apparaît 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 extérieure SELECT effectue les opérations suivantes :

  • Interroge l’ensemble de lignes, SalesOrderspécifié dans la FROM clause. Le résultat est un code XML avec un ou plusieurs <SalesOrder> éléments.

  • Spécifie le AUTO mode et la TYPE directive. AUTO transforme le résultat de la requête en XML, et la directive TYPE renvoie le résultat sous forme de type xml.

  • Inclut deux instructions imbriquées SELECT séparées par une virgule. Le premier imbriqué SELECT récupère les informations sur les commandes de vente, l’en-tête et les détails, et la deuxième instruction imbriquée SELECT récupère les informations du vendeur.

    • L'instruction SELECT qui récupère 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 les détails de commande client.

L’instruction SELECT qui récupère les informations du commercial interroge un ensemble de lignes, SalesPerson créé dans la FROM clause. Pour que les requêtes FOR XML fonctionnent, vous devez fournir un nom à 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 les commandes de vente, sauf que dans le code XML résultant, le <SalesPerson> apparaît comme un élément 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 TYPE directive retourne un résultat de requête en tant que xml type, vous pouvez interroger le code XML résultant à l’aide de différentes xml méthodes de type de données. Pour plus d’informations, consultez méthodes de type de données xml. Dans la requête suivante, notez ce qui suit :

  • La requête précédente est ajoutée dans la FROM clause. Le résultat de la requête est retourné sous forme de table. Notez l’alias XmlCol ajouté.

  • La clause SELECT spécifie un XQuery sur le XmlCol retourné dans la clause FROM. La query() méthode du xml type de données est utilisée pour spécifier le XQuery. Pour plus d'informations, voir la 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

Utiliser des requêtes FOR XML imbriquées

  • Last updated on