Requêtes FOR XML imbriquées

Article
12/15/2008

Dans SQL Server 2000, vous pouvez spécifier la clause FOR XML uniquement au niveau supérieur d'une requête SELECT. Le document XML obtenu est essentiellement renvoyé au client en vue de subir un traitement supplémentaire. Dans SQL Server 2005, qui introduit le type de données xml et la directive TYPE dans les requêtes FOR XML, le document XML renvoyé par les requêtes FOR XML peut faire l'objet d'un traitement supplémentaire sur le serveur.

Vous pouvez affecter le résultat de la requête FOR XML à une variable de type xml ou bien utiliser XQuery pour interroger le résultat et affecter celui-ci à une variable de type xml en vue d'un traitement supplémentaire.
```
DECLARE @x xml
SET @x=(SELECT ProductModelID, Name
        FROM Production.ProductModel
        WHERE ProductModelID=122 or ProductModelID=119
        FOR XML RAW, TYPE)
SELECT @x
-- Result
--<row ProductModelID="122" Name="All-Purpose Bike Stand" />
--<row ProductModelID="119" Name="Bike Wash" />
```
Vous pouvez poursuivre le traitement du document XML renvoyé dans la variable @x à l'aide de l'une des méthodes de type de données xml. Par exemple, vous pouvez extraire la valeur de l'attribut ProductModelID à l'aide de la méthode value() (type de données xml).
```
DECLARE @i int
SET @i = (SELECT @x.value('/row[1]/@ProductModelID[1]', 'int'))
SELECT @i
```
Dans l'exemple suivant, le résultat de la requête FOR XML renvoyé est de type xml car la directive TYPE est spécifiée dans la clause FOR XML.
```
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=119 or ProductModelID=122
FOR XML RAW, TYPE,ROOT('myRoot')
```
Voici le résultat obtenu :
```
<myRoot>
  <row ProductModelID="122" Name="All-Purpose Bike Stand" />
  <row ProductModelID="119" Name="Bike Wash" />
</myRoot>
```
Étant donné que le résultat est de type xml, vous pouvez spécifier l'une des méthodes de type de données xml directement par rapport à ce document XML, comme le montre la requête suivante. Dans la requête, la méthode query() (type de données xml) est utilisée pour extraire le premier élément enfant <row> de l'élément <myRoot>.
```
SELECT  (SELECT ProductModelID, Name
         FROM Production.ProductModel
         WHERE ProductModelID=119 or ProductModelID=122
         FOR XML RAW, TYPE,ROOT('myRoot')).query('/myRoot[1]/row[1]')
```
Voici le résultat obtenu :
```
<row ProductModelID="122" Name="All-Purpose Bike Stand" />
```
En outre, vous pouvez écrire des requêtes FOR XML imbriquées où le résultat de la requête interne est retourné en tant que type xml à la requête externe. Exemple :
```
SELECT Col1, 
       Col2, 
       ( SELECT Col3, Col4 
        FROM  T2
        WHERE T2.Col = T1.Col
        ...
        FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE
```
Vous remarquerez les points suivants relatifs à la requête précédente :
- Le XML généré par la requête FOR XML interne est ajouté au XML généré par la requête FOR XML externe.
- La requête interne spécifie la directive TYPE. Par conséquent, les données XML retournées par la requête interne sont du type xml. Si la directive TYPE n'est pas spécifiée, le résultat de la requête FOR XML interne est retourné en tant que nvarchar(max) et les données XML sont converties en entités.
Les requêtes FOR XML imbriquées vous permettent de mieux contrôler la forme des données XML résultantes.
- Dans SQL Server 2000, les requêtes en mode RAW et AUTO génèrent par défaut des données XML centrées sur l'attribut. Exemple :
```
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW
```
  Voici le résultat centré sur l'attribut obtenu :
```
<row ProductModelID="122" Name="All-Purpose Bike Stand" />
<row ProductModelID="119" Name="Bike Wash" />
```
  En spécifiant la directive ELEMENTS, vous pouvez récupérer toutes les données XML en tant que données centrées sur l'élément. Exemple :
```
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW, ELEMENTS 
```
  Voici le résultat centré sur l'élément obtenu :
```
<row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>
```
  Pour les requêtes FOR XML imbriquées dans SQL Server 2005, vous pouvez construire des données XML centrées partiellement sur l'attribut et partiellement sur l'élément.
- Dans SQL Server 2000, vous pouvez construire des frères uniquement en écrivant des requêtes en mode EXPLICIT. Toutefois, cette méthode peut s'avérer lourde. Dans SQL Server 2005, vous pouvez générer des hiérarchies XML qui incluent des frères en spécifiant des requêtes FOR XML en mode AUTO imbriquées.
Quel que soit le mode utilisé, les requêtes FOR XML imbriquées procurent davantage de contrôle dans la description de la forme des données XML résultantes. Elles peuvent être utilisées à la place des requêtes en mode EXPLICIT.

Exemples

A. Comparaison d'une requête FOR XML à une requête FOR XML imbriquée

La requête SELECT suivante extrait de la base de données AdventureWorks des informations sur les catégories et les sous-catégories de produits. La requête ne contient aucune clause FOR XML imbriquée.

SELECT   ProductCategory.ProductCategoryID, 
         ProductCategory.Name as CategoryName,
         ProductSubCategory.ProductSubCategoryID, 
         ProductSubCategory.Name
FROM     Production.ProductCategory, Production.ProductSubCategory
WHERE    ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryID
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE
GO

Voici le résultat partiel :

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory ProductSubCategoryID="1" Name="Mountain Bike"/>
  <ProductSubCategory ProductSubCategoryID="2" Name="Road Bike"/>
  <ProductSubCategory ProductSubCategoryID="3" Name="Touring Bike"/>
</ProductCategory>
...

Si vous spécifiez la directive ELEMENTS dans la requête, vous recevez un résultat centré sur l'élément, comme le montre le fragment de résultat suivant :

<ProductCategory>
  <ProductCategoryID>1</ProductCategoryID>
  <CategoryName>Bike</CategoryName>
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <Name>Mountain Bike</Name>
  </ProductSubCategory>
  <ProductSubCategory>
     ...
  </ProductSubCategory>
</ProductCategory>

Ensuite, supposons que vous souhaitiez générer une hiérarchie XML qui combine des données XML centrées sur l'attribut et centrées sur l'élément, comme le montre le fragment suivant :

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

Dans le fragment précédent, les informations relatives aux catégories de produits, telles que l'ID de catégorie et le nom de catégorie, sont des attributs. Toutefois, les informations sur les sous-catégories sont centrées sur l'élément. Pour construire l'élément <ProductCategory>, vous pouvez écrire une requête FOR XML, comme l'illustre le code suivant :

SELECT ProductCategoryID, Name as CategoryName
FROM Production.ProductCategory ProdCat
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

Voici le résultat obtenu :

< ProdCat ProductCategoryID="1" CategoryName="Bikes" />
< ProdCat ProductCategoryID="2" CategoryName="Components" />
< ProdCat ProductCategoryID="3" CategoryName="Clothing" />
< ProdCat ProductCategoryID="4" CategoryName="Accessories" />

Ensuite, pour construire les éléments <ProductSubCategory> imbriqués dans le document XML de votre choix, vous ajoutez une requête FOR XML imbriquée, comme le montre le code suivant :

SELECT ProductCategoryID, Name as CategoryName,
       (SELECT ProductSubCategoryID, Name SubCategoryName
        FROM   Production.ProductSubCategory
        WHERE ProductSubCategory.ProductCategoryID = 
              ProductCategory.ProductCategoryID
        FOR XML AUTO, TYPE, ELEMENTS
       )
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

Notez les points suivants par rapport à la requête ci-dessus :

La requête FOR XML interne extrait des informations sur les sous-catégories de produits. La directive ELEMENTS est ajoutée à la requête FOR XML interne pour générer des données XML centrées sur l'élément, qui sont ajoutées aux données XML créées par la requête externe. Par défaut, la requête externe génère des données XML centrées sur l'attribut.
Dans la requête interne, la directive TYPE est spécifiée de manière à ce que le résultat soit de type xml. Si la directive TYPE n'est pas spécifiée, le résultat est de type nvarchar(max) et les données XML sont renvoyées sous la forme d'entités.
La requête externe spécifie également la directive TYPE. Par conséquent, le résultat de cette requête renvoyé au client est de type xml.

Voici le résultat partiel :

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

La requête suivante est simplement une extension de la requête précédente. Elle montre la hiérarchie complète des produits stockés dans la base de données AdventureWorks. Notamment :

Les catégories de produits
Les sous-catégories de produits dans chaque catégorie
Les modèles de produits dans chaque sous-catégorie
Les produits dans chaque modèle

La requête suivante peut s'avérer utile pour comprendre la base de données AdventureWorks :

SELECT ProductCategoryID, Name as CategoryName,
       (SELECT ProductSubCategoryID, Name SubCategoryName,
               (SELECT ProductModel.ProductModelID, 
                       ProductModel.Name as ModelName,
                       (SELECT ProductID, Name as ProductName, Color
                        FROM   Production.Product
                        WHERE  Product.ProductModelID = 
                               ProductModel.ProductModelID
                        FOR XML AUTO, TYPE)
                FROM   (SELECT distinct ProductModel.ProductModelID, 
                               ProductModel.Name
                        FROM   Production.ProductModel, 
                               Production.Product
                        WHERE  ProductModel.ProductModelID = 
                               Product.ProductModelID
                        AND    Product.ProductSubCategoryID = 
                               ProductSubCategory.ProductSubCategoryID) 
                                  ProductModel
                FOR XML AUTO, type
               )
        FROM Production.ProductSubCategory
        WHERE ProductSubCategory.ProductCategoryID = 
              ProductCategory.ProductCategoryID
        FOR XML AUTO, TYPE, ELEMENTS
       )
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

Voici le résultat partiel :

<Production.ProductCategory ProductCategoryID="1" CategoryName="Bikes">
  <Production.ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bikes</SubCategoryName>
    <ProductModel ProductModelID="19" ModelName="Mountain-100">
      <Production.Product ProductID="771" 
                ProductName="Mountain-100 Silver, 38" Color="Silver" />
      <Production.Product ProductID="772" 
                ProductName="Mountain-100 Silver, 42" Color="Silver" />
      <Production.Product ProductID="773" 
                ProductName="Mountain-100 Silver, 44" Color="Silver" />
        …
    </ProductModel>
     …

Si vous supprimez la directive ELEMENTS de la requête FOR XML imbriquée qui génère les sous-catégories de produits, la totalité du résultat est centrée sur l'attribut. Vous pouvez ensuite écrire cette requête sans imbrication. L'ajout de la directive ELEMENTS aboutit à un document XML en partie centré sur l'attribut et en partie centré sur l'élément. Ce résultat ne peut pas être généré par une requête FOR XML d'un seul niveau.

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

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

Les informations sur les détails 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 document XML et la directive TYPE renvoie 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)

C. Création d'une application ASPX pour extraire les informations sur la commande dans le navigateur

Dans l'exemple suivant, une application aspx exécute une procédure stockée et renvoie les informations sur la commande sous la forme de données XML. Le résultat est affiché dans le navigateur. L'instruction SELECT figurant dans la procédure stockée est similaire à celle de l'exemple B, sauf que le document XML obtenu est centré sur l'élément.

CREATE PROC GetSalesOrderInfo AS
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, ELEMENTS)
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
GO

Voici l'application .aspx. Elle exécute la procédure stockée et renvoie les données XML dans le navigateur :

<%@LANGUAGE=C# Debug=true %>
<%@import Namespace="System.Xml"%>
<%@import namespace="System.Data.SqlClient" %><%
Response.Expires = -1;
Response.ContentType = "text/xml";
%>

<%
using(System.Data.SqlClient.SqlConnection c = new System.Data.SqlClient.SqlConnection("Data Source=server;Database=AdventureWorks;Integrated Security=SSPI;"))
using(System.Data.SqlClient.SqlCommand cmd = c.CreateCommand())
{
   cmd.CommandText = "GetSalesOrderInfo";
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Connection.Open();
   System.Xml.XmlReader r = cmd.ExecuteXmlReader();
   System.Xml.XmlTextWriter w = new System.Xml.XmlTextWriter(Response.Output);
   w.WriteStartElement("Root");
   r.MoveToContent();
   while(! r.EOF)
   {
      w.WriteNode(r, true);
   }
   w.WriteEndElement();
   w.Flush();
}
%>

Pour tester l'application

Créez la procédure stockée dans la base de données AdventureWorks.
Enregistrez l'application .aspx dans le répertoire c:\inetpub\wwwroot (GetSalesOrderInfo.aspx).
Exécutez l'application ((https://server/GetSalesOrderInfo.aspx).

D. Construction du document XML qui comprend les prix des produits

L'exemple suivant interroge la table Production.Product pour extraire les valeurs ListPrice et StandardCost d'un produit spécifique. Pour que la requête soit intéressante, les deux prix sont renvoyés dans un élément <Price> et chaque élément <Price> possède un attribut PriceType. Voici la forme attendue du document XML :

<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" elementFormDefault="qualified">
  <xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="Production.Product" type="xsd:anyType" />
</xsd:schema>
<Production.Product xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" ProductID="520">
  <Price  PriceType="ListPrice">133.34</Price>
  <Price  PriceType="StandardCost">98.77</Price>
</Production.Product>

Voici la requête FOR XML imbriquée :

SELECT Product.ProductID, 
          (SELECT 'ListPrice' as PriceType, 
                   CAST(CAST(ListPrice as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE),
          (SELECT  'StandardCost' as PriceType, 
                   CAST(CAST(StandardCost as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE)
FROM Production.Product
WHERE ProductID=520
for XML AUTO, TYPE, XMLSCHEMA

Notez les points suivants par rapport à la requête ci-dessus :

L'instruction SELECT externe construit l'élément <Product> qui possède un attribut ProductID et deux éléments enfants <Price>.
Les deux instructions SELECT internes construisent deux éléments <Price>, possédant chacun un attribut PriceType et des données XML qui renvoient le prix du produit.
La directive XMLSCHEMA figurant dans l'instruction SELECT externe génère le schéma XSD en ligne qui décrit la forme du document XML obtenu.

Pour que la requête soit intéressante, vous pouvez écrire la requête FOR XML puis écrire une requête XQuery par rapport au résultat afin de redéfinir la forme du document XML, comme le montre la requête suivante :

SELECT ProductID, 
 ( SELECT p2.ListPrice, p2.StandardCost
   FROM Production.Product p2 
   WHERE Product.ProductID = p2.ProductID
   FOR XML AUTO, ELEMENTS XSINIL, type ).query('
                                   for $p in /p2/*
                                   return 
                                    <Price PriceType = "{local-name($p)}">
                                     { data($p) }
                                    </Price>
                                  ')
FROM Production.Product
WHERE ProductID = 520
FOR XML AUTO, TYPE

L'exemple précédent utilise la méthode query() du type de données xml pour interroger le document XML renvoyé par la requête FOR XML interne et construire le résultat attendu.

Voici le résultat obtenu :

<Production.Product ProductID="520">
  <Price PriceType="ListPrice">133.3400</Price>
  <Price PriceType="StandardCost">98.7700</Price>
</Production.Product>

Share via

Requêtes FOR XML imbriquées

Exemples

A. Comparaison d'une requête FOR XML à une requête FOR XML imbriquée

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

C. Création d'une application ASPX pour extraire les informations sur la commande dans le navigateur

Pour tester l'application

D. Construction du document XML qui comprend les prix des produits

Ressources supplémentaires