Partager via

Forme XML avec des requêtes FOR XML imbriquées

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

L'exemple suivant interroge la table Production.Product pour extraire les valeurs ListPrice et StandardCost d'un produit spécifique. Pour rendre la requête intéressante, les deux prix sont retournés dans un <Price> élément, et chaque <Price> élément a un PriceType attribut.

Exemple

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 xmlns="" PriceType="ListPrice">133.34</Price>
  <Price xmlns="" PriceType="StandardCost">98.77</Price>
</Production.Product>

Voici la requête FOR XML imbriquée :

USE AdventureWorks2022;
GO
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 dans la requête précédente :

  • L’instruction SELECT externe construit l’élément <Product> qui a un attribut ProductID et deux <Price> éléments enfants.

  • Les deux instructions SELECT internes construisent deux <Price> éléments, chacun avec un attribut PriceType et xml qui retourne 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 rendre la requête 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 query() méthode du type de données xml pour interroger le code XML retourné 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>

Voir aussi