Façonner des données XML avec des requêtes FOR XML imbriquées
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 AdventureWorks2012;
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 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>