Exemples : utilisation du mode PATH

Article
07/15/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Les exemples suivants montrent comment utiliser le mode PATH pour générer un document XML à partir d'une requête SELECT. Nombre de ces requêtes sont spécifiées par rapport aux documents XML des instructions de fabrication de bicyclettes stockés dans la colonne Instructions de la table ProductModel.

Spécifier une requête en mode PATH

Cette requête spécifie un mode FOR XML PATH.

USE AdventureWorks2022;
GO
SELECT
       ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH;
GO

Le résultat suivant est un document XML centré sur l'élément dans lequel chaque valeur de colonne de l'ensemble de lignes obtenu est incluse dans un élément. Étant donné que la clause SELECT ne spécifie aucun alias pour les noms de colonnes, les noms d'éléments enfants générés sont les mêmes que les noms de colonnes correspondants dans la clause SELECT . Pour chaque ligne de l'ensemble de lignes, une balise <row> est ajoutée.

<row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>

Le résultat suivant est le même que la requête en mode RAW avec l'option ELEMENTS spécifiée. Il renvoie un document XML centré sur l'élément avec un élément <row> par défaut pour chaque ligne du jeu de résultats.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML RAW, ELEMENTS;

Vous pouvez éventuellement spécifier le nom d'élément de ligne pour remplacer l'élément <row> par défaut. Par exemple, la requête suivante renvoie l'élément <ProductModel> de chaque ligne de l'ensemble de lignes.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModel');
GO

Le document XML obtenu possède un nom d'élément de ligne spécifié.

<ProductModel>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</ProductModel>
<ProductModel>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</ProductModel>

Si vous spécifiez une chaîne nulle, l'élément d'habillage n'est pas généré.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('');
GO

Voici le résultat obtenu :

<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>

Spécification de noms de colonnes de type XPath

Dans la requête suivante, le nom de colonne ProductModelID spécifié commence par « @ » et ne contient pas de barre oblique (/). Par conséquent, un attribut de l'élément <row> ayant la valeur de colonne correspondante est créé dans le document XML obtenu.

USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@id",
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('ProductModelData');
GO

Voici le résultat obtenu :

<ProductModelData id="122">
  <Name>All-Purpose Bike Stand</Name>
</ProductModelData>
<ProductModelData id="119">
  <Name>Bike Wash</Name>
</ProductModelData>

Vous pouvez ajouter un élément de niveau supérieur unique en spécifiant l'option root dans FOR XML.

SELECT ProductModelID AS "@id",
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModelData'), root ('Root');
GO

Pour générer une hiérarchie, vous pouvez inclure une syntaxe de type PATH. Par exemple, remplacez le nom de la colonne Name par « SomeChild/ModelName » afin d'obtenir un document XML avec hiérarchie, comme le montre le résultat suivant :

<Root>
  <ProductModelData id="122">
    <SomeChild>
      <ModelName>All-Purpose Bike Stand</ModelName>
    </SomeChild>
  </ProductModelData>
  <ProductModelData id="119">
    <SomeChild>
      <ModelName>Bike Wash</ModelName>
    </SomeChild>
  </ProductModelData>
</Root>

Outre l'ID et le nom du modèle de produit, la requête suivante extrait les emplacements des instructions de fabrication du modèle. Étant donné que la colonne Instructions est de type xml, la méthode query() de type de données xml est spécifiée pour extraire l'emplacement.

SELECT ProductModelID AS "@id",
       Name,
       Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
                /MI:root/MI:Location
              ') AS ManuInstr
FROM Production.ProductModel
WHERE ProductModelID = 7
FOR XML PATH ('ProductModelData'), root ('Root');
GO

Le résultat partiel est le suivant. Étant donné que la requête spécifie ManuInstr comme nom de colonne, le document XML renvoyé par la méthode query() est inclus dans une <ManuInstr> balise comme le montre la syntaxe suivante :

<Root>
  <ProductModelData id="7">
    <Name>HL Touring Frame</Name>
    <ManuInstr>
      <MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
        <MI:step>...</MI:step>...
      </MI:Location>
      ...
    </ManuInstr>
  </ProductModelData>
</Root>

Dans la requête FOR XML précédente, vous pouvez inclure des espaces de noms pour les éléments <Root> et <ProductModelData>. Pour ce faire, vous pouvez définir la liaison préfixe/espace de noms à l'aide de WITH XMLNAMESPACES puis utiliser des préfixes dans la requête FOR XML. Pour plus d’informations, consultez Ajouter des espaces de noms aux requêtes avec WITH XMLNAMESPACES.

USE AdventureWorks2022;
GO
WITH XMLNAMESPACES (
   'uri1' AS ns1,
   'uri2' AS ns2,
   'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as MI)
SELECT ProductModelID AS "ns1:ProductModelID",
       Name           AS "ns1:Name",
       Instructions.query('
                /MI:root/MI:Location
              ')
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH ('ns2:ProductInfo'), root('ns1:root');
GO

Le préfixe MI est également défini dans WITH XMLNAMESPACES. Par conséquent, la méthode query() du type xml spécifié ne définit pas le préfixe dans le prologue de la requête. Voici le résultat obtenu :

<ns1:root xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" xmlns="uri2" xmlns:ns2="uri2" xmlns:ns1="uri1">
  <ns2:ProductInfo>
    <ns1:ProductModelID>7</ns1:ProductModelID>
    <ns1:Name>HL Touring Frame</ns1:Name>
    <MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" LaborHours="2.5" LotSize="100" MachineHours="3" SetupHours="0.5" LocationID="10" xmlns="">
    <MI:step>
      Insert <MI:material>aluminum sheet MS-2341</MI:material> into the <MI:tool>T-85A framing tool</MI:tool>.
    </MI:step>
    ...
    </MI:Location>
    ...
  </ns2:ProductInfo>
</ns1:root>

Générer une liste de valeurs à l’aide du mode PATH

Pour chaque modèle de produit, cette requête construit une liste de valeurs d'ID de produit. Pour chaque ID de produit, la requête construit également des éléments imbriqués <ProductName>, comme le montre le fragment XML suivant :

<ProductModelData ProductModelID="7" ProductModelName="..." ProductIDs="product id list in the product model">
  <ProductName>...</ProductName>
  <ProductName>...</ProductName>
  ...
</ProductModelData>

La requête ci-après génère le document XML souhaité :

USE AdventureWorks2022;
GO
SELECT ProductModelID     AS "@ProductModelID",
       Name               AS "@ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')) AS "@ProductIDs",
       (SELECT Name AS "ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
        FOR XML PATH ('')) AS "ProductNames"
FROM   Production.ProductModel
WHERE  ProductModelID= 7 or ProductModelID=9
FOR XML PATH('ProductModelData');

Notez les points suivants dans la requête précédente :

La première clause SELECT imbriquée renvoie une liste d'identificateurs ProductID en utilisant data() comme nom de colonne. Étant donné que la requête spécifie une chaîne vide comme nom d'élément de ligne dans FOR XML PATH, aucun élément n'est généré. À la place, la liste de valeurs est affectée à l'attribut ProductID .
La seconde clause SELECT imbriquée extrait les noms des produits du modèle concerné. Elle génère des éléments <ProductName> qui sont renvoyés inclus dans l'élément <ProductNames>, car la requête spécifie ProductNames comme nom de colonne.

Voici le résultat partiel :

<ProductModelData PId="7" ProductModelName="HL Touring Frame" ProductIDs="885 887 ...">
  <ProductNames>
    <ProductName>HL Touring Frame - Yellow, 60</ProductName>
    <ProductName>HL Touring Frame - Yellow, 46</ProductName>
  </ProductNames>
  ...
</ProductModelData>
<ProductModelData PId="9" ProductModelName="LL Road Frame" ProductIDs="722 723 724 ...">
  <ProductNames>
    <ProductName>LL Road Frame - Black, 58</ProductName>
    <ProductName>LL Road Frame - Black, 60</ProductName>
    <ProductName>LL Road Frame - Black, 62</ProductName>
    ...
  </ProductNames>
</ProductModelData>

La sous-requête qui construit les noms de produits renvoie le résultat sous la forme d'une chaîne décomposée en entités puis ajoutée au document XML. Si vous ajoutez la directive type, FOR XML PATH (''), type, la sous-requête retourne le résultat en tant que type xml et aucune décomposition en entités ne se produit.

USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@ProductModelID",
      Name AS "@ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')
       ) AS "@ProductIDs",
       (
       SELECT Name AS "ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH (''), type
       ) AS "ProductNames"

FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData');

Ajout d'espaces de noms au document XML obtenu

Comme l’explique la rubrique Ajout d’espaces de noms à l’aide de WITH XMLNAMESPACES, vous pouvez utiliser WITH XMLNAMESPACES pour inclure des espaces de noms dans les requêtes en mode PATH. Par exemple, les noms spécifiés dans la clause SELECT comprennent des préfixes d'espace de noms. La requête en mode PATH suivante construit un document XML avec des espaces de noms.

SELECT 'en'    as "English/@xml:lang",
       'food'  as "English",
       'ger'   as "German/@xml:lang",
       'Essen' as "German"
FOR XML PATH ('Translation')
GO

L'attribut @xml:lang ajouté à l'élément <English> est défini dans l'espace de noms xml prédéfini.

Voici le résultat obtenu :

<Translation>
  <English xml:lang="en">food</English>
  <German xml:lang="ger">Essen</German>
</Translation>

La requête suivante est similaire à l'exemple C, sauf qu'elle utilise WITH XMLNAMESPACES pour inclure des espaces de noms dans le résultat XML. Pour plus d’informations, consultez Ajouter des espaces de noms aux requêtes avec WITH XMLNAMESPACES.

USE AdventureWorks2022;
GO
WITH XMLNAMESPACES ('uri1' AS ns1,  DEFAULT 'uri2')
SELECT ProductModelID AS "@ns1:ProductModelID",
      Name AS "@ns1:ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')
       ) AS "@ns1:ProductIDs",
       (
       SELECT ProductID AS "@ns1:ProductID",
              Name AS "@ns1:ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH , type
       ) AS "ns1:ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData'), root('root');

Voici le résultat obtenu :

<root xmlns="uri2"
  xmlns:ns1="uri1">
  <ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">
    <ns1:ProductNames>
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />
      ...
    </ns1:ProductNames>
  </ProductModelData>
  <ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">
    <ns1:ProductNames>
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />
      ...
    </ns1:ProductNames>
  </ProductModelData>
</root>

Voir aussi

Utiliser le mode PATH avec FOR XML

Partager via

Exemples : utilisation du mode PATH

Spécifier une requête en mode PATH

Spécification de noms de colonnes de type XPath

Générer une liste de valeurs à l’aide du mode PATH

Ajout d'espaces de noms au document XML obtenu

Voir aussi

Commentaires

Ressources supplémentaires