Génération de frères à l'aide d'une requête imbriquée en mode AUTO
L'exemple suivant indique comment générer des frères à l'aide d'une requête imbriquée en mode AUTO. La seule autre façon de générer ce type de document XML consiste à utiliser le mode EXPLICIT. Toutefois, cette méthode peut s'avérer lourde.
Exemple
Cette requête construit le document XML qui fournit les informations sur les commandes. Notamment :
Informations d'en-tête de commande client, SalesOrderID, SalesPersonID et OrderDate. AdventureWorks2008R2 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 commercial. Il s'agit du vendeur qui a traité la commande.
Les deux requêtes 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 <SalesOrderHeader> apparaît en tant qu'enfant frère de <SalesOrder> :
USE AdventureWorks2008R2;
GO
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)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
FROM Sales.SalesOrderHeader, Sales.SalesPerson
WHERE SalesOrderHeader.SalesPersonID = SalesPerson.BusinessEntityID
) 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 la 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 récupère SalesOrderID, SalesPersonID et CustomerID inclut elle-même une autre instruction SELECT imbriquée. Instruction FOR XML (avec le mode AUTO et la directive TYPE) qui retourne les informations du détail de la commande client.
L'instruction SELECT qui extrait les informations sur le commercial 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="29825">
<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>
</SalesOrder>
<SalesOrder>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="29672">
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
</Sales.SalesOrderHeader>
</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 :
USE AdventureWorks2008R2;
GO
SELECT 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 SalesOrderID=43659 OR SalesOrderID=43660
FOR XML AUTO, TYPE;
Voici le résultat obtenu :
<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="29825">
<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>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="29672">
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
</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) FROM Sales.SalesOrderHeader WHERE SalesOrderID='43659' or SalesOrderID='43660' FOR XML AUTO, TYPE ) as T(XmlCol);