Méthode nodes() (type de données xml)

  • Article

La méthode nodes() est utile pour éclater une instance de type de données xml en données relationnelles. Elle vous permet d'identifier les nœuds à mapper dans une nouvelle ligne.

Chaque instance de type de données xml possède un nœud de contexte fourni implicitement. Pour l'instance XML stockée dans une colonne ou variable, il s'agit du nœud de document. Le nœud de document est le nœud implicite situé en haut de chaque instance de type de données xml.

Le résultat de la méthode nodes() est un ensemble de lignes qui contient des copies logiques des instances XML d'origine. Dans ces copies logiques, le nœud de contexte de chaque instance de ligne correspond à l'un des nœuds identifiés avec l'expression de requête, ce qui permet aux requêtes ultérieures de naviguer par rapport à ces nœuds de contexte.

Vous pouvez extraire plusieurs valeurs de l'ensemble de lignes. Par exemple, vous pouvez appliquer la méthode value() à l'ensemble de lignes renvoyé par nodes() puis extraire plusieurs valeurs de l'instance XML d'origine. Appliquée à l'instance XML, la méthode value() ne renvoie qu'une valeur.

Voici la syntaxe générale :

nodes (XQuery) as Table(Column)
  • XQuery
    Littéral de chaîne, représentant une expression XQuery. Si l'expression de requête construit des nœuds, ceux-ci sont exposés dans l'ensemble de lignes obtenu. Si l'expression de requête aboutit à une séquence vide, l'ensemble de lignes est vide. Si l'expression de requête aboutit de façon statique à une séquence qui contient des valeurs atomiques au lieu de nœuds, une erreur statique est déclenchée.
  • Table(Column)
    Nom de table et nom de colonne de l'ensemble de lignes obtenu.

Prenons par exemple la table suivante :

T (ProductModelID int, Instructions xml)

Le document des instructions de fabrication suivant est stocké dans la table. Seule une partie de celui-ci est montrée. Le document indique trois sites de fabrication.

<root>
  <Location LocationID="10"...>
     <step>...</step>
     <step>...</step>
      ...
  </Location>
  <Location LocationID="20" ...>
       ...
  </Location>
  <Location LocationID="30" ...>
       ...
  </Location>
</root>

Un appel de la méthode nodes() avec l'expression de requête /root/Location renverrait un ensemble de lignes composé de trois lignes, contenant chacune une copie logique du document XML d'origine et associant l'élément de contexte à l'un des nœuds <Location> :

Product
ModelID      Instructions
----------------------------------
1       <root>
             <Location LocationID="20" ... />
             <Location LocationID="30" .../></root>
1      <root><Location LocationID="10" ... />
             
             <Location LocationID="30" .../></root>
1      <root><Location LocationID="10" ... />
             <Location LocationID="20" ... />
             </root>

Vous pouvez alors interroger cet ensemble de lignes à l'aide de méthodes de type de données xml. La requête suivante extrait la sous-arborescence de l'élément de contexte pour chaque ligne générée :

SELECT T2.Loc.query('.')
FROM   T
CROSS APPLY Instructions.nodes('/root/Location') as T2(Loc)

Voici le résultat obtenu :

ProductModelID  Instructions
----------------------------------
1        <Location LocationID="10" ... />
1        <Location LocationID="20" ... />
1        <Location LocationID="30" .../>

Notes

L'ensemble de lignes renvoyé a conservé les informations de type. Vous pouvez appliquer des méthodes de type de données xml telles que query(), value(), exist() et nodes(), au résultat d'une méthode nodes(). Toutefois, vous ne pouvez pas appliquer la méthode modify() pour modifier l'instance XML.

En outre, le nœud de contexte figurant dans l'ensemble de lignes ne peut pas être matérialisé. Vous ne pouvez donc pas l'utiliser dans une instruction SELECT. Toutefois, vous pouvez l'utiliser dans IS NULL et COUNT(*).

Les scénarios d'utilisation des méthodes nodes() sont les mêmes que ceux de OPENXML, qui fournit une vue d'ensemble de lignes du document XML. Toutefois, vous n'avez pas besoin de recourir à des curseurs lorsque vous utilisez la méthode nodes() sur une table qui contient plusieurs lignes de documents XML.

L'ensemble de lignes renvoyé par la méthode nodes() est un ensemble de lignes sans nom. Par conséquent, il doit être explicitement nommé à l'aide d'alias.

La fonction nodes() ne peut pas s'appliquer directement aux résultats d'une fonction définie par l'utilisateur. Pour utiliser la fonction nodes() avec le résultat d'une fonction scalaire définie par l'utilisateur, vous pouvez soit attribuer le résultat à une variable, soit exploiter une table dérivée pour affecter un alias de colonne à la valeur retournée de la fonction définie par l'utilisateur puis utiliser CROSS APPLY qui effectuera une sélection à partir de l'alias.

L'exemple suivant illustre une utilisation de CROSS APPLY permettant d'opérer une sélection à partir du résultat d'une fonction définie par l'utilisateur.

USE AdventureWorks;
GO

CREATE FUNCTION XTest()
RETURNS xml
AS
BEGIN
RETURN '<document/>';
END;
GO


SELECT A2.B.query('.')
FROM
(SELECT dbo.XTest()) AS A1(X) 
CROSS APPLY X.nodes('.') A2(B);
GO

DROP FUNCTION XTest;
GO

Exemples

A. Utilisation de la méthode nodes() par rapport à une variable de type xml

Dans l'exemple suivant figure un document XML qui possède un élément de niveau supérieur <Root> et trois éléments enfants <row>. La requête utilise la méthode nodes() pour définir un nœud de contexte distinct par élément <row>. La méthode nodes() renvoie un ensemble de lignes composé de trois lignes. Chaque ligne possède une copie logique du document XML d'origine et chaque nœud de contexte identifie un élément <row> distinct de ce document.

La requête renvoie ensuite le nœud de contexte depuis chaque ligne :

DECLARE @x xml 
SET @x='<Root>
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>
    <row id="2"><name>moe</name></row>
    <row id="3" />
</Root>'
SELECT T.c.query('.') AS result
FROM   @x.nodes('/Root/row') T(c)
GO

Le résultat est le suivant. dans cet exemple, la méthode query renvoie l'élément de contexte et son contenu :

 <row id="1"><name>Larry</name><oflw>some text</oflw></row>
 <row id="2"><name>moe</name></row>
 <row id="3"/>

L'application de l'accesseur parent aux nœuds de contexte renvoie l'élément <Root> pour les trois lignes :

SELECT T.c.query('..') AS result
FROM   @x.nodes('/Root/row') T(c)
go

Voici le résultat obtenu :

<Root>
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>
    <row id="2"><name>moe</name></row>
    <row id="3" />
</Root>
<Root>
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>
    <row id="2"><name>moe</name></row>
    <row id="3" />
</Root>
<Root>
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>
    <row id="2"><name>moe</name></row>
    <row id="3" />
</Root>

La requête suivante spécifie un chemin d'accès absolu. Une requête sur un nœud de contexte qui utilise une expression de chemin d'accès absolu démarre au nœud racine de ce nœud. Par conséquent, vous recevez la totalité des trois lignes pour chaque nœud de contexte renvoyé par nodes().

SELECT T.c.query('/Root/row') AS result
FROM   @x.nodes('/Root/row') T(c)
GO
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />

La colonne renvoyée par la méthode nodes() du type de données xml ne peut pas être utilisée directement. Par exemple, la requête suivante renvoie une erreur :

...
SELECT T.c
FROM   @x.nodes('/Root/row') T(c)

Dans la requête suivante, les méthodes value() et query() du type de données xml sont appliquées à l'ensemble de lignes renvoyé par la méthode nodes(). La méthode value() renvoie l'attribut id de l'élément de contexte (<row>), tandis que la méthode query() renvoie la sous-arborescence d'élément <name> de l'élément de contexte.

DECLARE @x xml 
SET @x='
<Root>
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>
    <row id="2"><name>Joe</name></row>
    <row id="3" />
</Root>
'
SELECT T.c.value('@id','int') as id,
       T.c.query('name') as NAME
FROM   @x.nodes('/Root/row') T(c)
GO

Voici le résultat obtenu :

 id  NAME
-----------------------
 1   <name>Larry</name>
 2   <name>Joe</name>
 3

Le résultat comprend l'ID de ligne 3 et l'élément <row> ne possède pas d'enfant <name>. Pour filtrer le résultat afin de renvoyer, ou de ne pas renvoyer, les lignes qui ne contiennent pas l'enfant <name>, vous pouvez procéder de l'une des manières suivantes :

  • Utilisez un prédicat dans l'expression de chemin d'accès nodes() tel que /Root/row[name].
  • Utilisez la méthode exist() sur l'ensemble de lignes.
  • Utilisez CROSS APPLY.
  • Utilisez OUTER APPLY.

La requête suivante spécifie la méthode exist() par rapport à l'ensemble de lignes renvoyé par nodes(). La méthode exist() renvoie True si le nœud de contexte (<row>) possède un enfant <name>.

DECLARE @x xml        
SET @x='<Root>       
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>       
    <row id="2"><name>Joe</name></row>       
    <row id="3" />       
</Root>'       
SELECT T1.rows.value('@id','int') as id       
FROM @x.nodes('/Root/row') T1(rows)       
WHERE T1.rows.exist('name') = 1;       
GO

Cette requête renvoie les deux lignes d'ID 1 et 2.

La requête suivante utilise OUTER APPLY. OUTER APPLY applique nodes() à chaque ligne de T1(rows) et renvoie des lignes qui génèrent l'ensemble de résultats ainsi que la valeur NULL. Par conséquent, la clause WHERE est utilisée pour filtrer les lignes et extraire uniquement celles dont la colonne T2.names n'a pas pour valeur NULL.

DECLARE @x xml        
SET @x='       
<Root>       
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>       
    <row id="2"><name>Joe</name></row>       
    <row id="3" />       
</Root>'       
SELECT T1.rows.value('@id','int') as id       
FROM @x.nodes('/Root/row') T1(rows)       
OUTER APPLY T1.rows.nodes('./name') as T2(names)       
WHERE T2.names IS NOT NULL        
GO

La requête suivante utilise CROSS APPLY. CROSS APPLY applique nodes() à chaque ligne de la table externe, T1(rows), et renvoie uniquement les lignes qui génèrent un ensemble de résultats lorsque nodes() est appliquée à T1.rows. Dans ce cas, il n'est pas nécessaire d'utiliser la clause WHERE pour tester IS NOT NULL.

DECLARE @x xml        
SET @x='<Root>       
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>       
    <row id="2"><name>Joe</name></row>       
    <row id="3" />       
</Root>'       
SELECT T1.rows.value('@id','int') as id       
FROM @x.nodes('/Root/row') T1(rows)       
CROSS APPLY T1.rows.nodes('./name') as T2(names)       
GO

Pour plus d'informations sur CROSS APPLY et OUTER APPLY, consultez Utilisation de APPLY.

B. Spécification de la méthode nodes() par rapport à une colonne de type xml

Cet exemple utilise les instructions de fabrication de bicyclette, qui sont stockées dans la colonne Instructions de type xml de la table ProductModel. Pour plus d'informations, consultez Représentation du type de données xml dans la base de données AdventureWorks.

Dans l'exemple suivant, la méthode nodes() est spécifiée par rapport à la colonne Instructions de type xml de la table ProductModel.

La méthode nodes() définit les éléments <Location> en tant que nœuds de contexte en spécifiant le chemin d'accès /MI:root/MI:Location. L'ensemble de lignes obtenu comprend une copie logique du document d'origine par nœud <Location> du document et le nœud de contexte a pour valeur l'élément <Location>. Par conséquent, la fonction nodes() fournit un ensemble de nœuds de contexte <Location>.

La méthode query() appliquée à cet ensemble de lignes demande self::node et, par conséquent, renvoie l'élément <Location> de chaque ligne.

Dans cet exemple, la requête définit chaque élément <Location> en tant que nœud de contexte du document d'instructions de fabrication d'un modèle de produit spécifique. Ces nœuds de contexte vous permettent d'effectuer les opérations d'extraction suivantes :

  • Rechercher les identificateurs de localisation dans chaque élément <Location>.
  • Extraire les étapes de fabrication (éléments enfants <step>) dans chaque élément <Location>.

Cette requête renvoie l'élément de contexte, dans lequel est spécifiée la syntaxe abrégée '.' de self::node(), dans la méthode query().

Notez les points suivants :

  • La méthode nodes() est appliquée à la colonne Instructions et renvoie l'ensemble de lignes T (C). Cet ensemble de lignes contient des copies logiques du document d'origine des instructions de fabrication et indique /root/Location en guise d'élément de contexte.

  • CROSS APPLY applique nodes() à chaque ligne de la table Instructions et renvoie uniquement les lignes qui génèrent un ensemble de résultats.

    SELECT C.query('.') as result
FROM Production.ProductModel
CROSS APPLY Instructions.nodes('
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location') as T(C)
WHERE ProductModelID=7

    Voici le résultat partiel :

    <MI:Location LocationID="10"  ...>
   <MI:step ... />
      ...
</MI:Location>
<MI:Location LocationID="20"  ... >
    <MI:step ... />
      ...
</MI:Location>
...

La requête suivante est similaire à la requête précédente, sauf qu'elle utilise value() et query() pour extraire un ensemble de valeurs à l'aide des nœuds de contexte de l'ensemble de lignes. La clause SELECT extrait l'ID de chaque site et les outils qui y sont utilisés.

SELECT C.value('@LocationID','int') as LId,
       C.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
                 MI:step/MI:tool') as result
FROM    Production.ProductModel
CROSS APPLY Instructions.nodes('
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location') as T(C)
WHERE ProductModelID=7

Le résultat est le suivant. dépourvu des espaces de noms pour des raisons de lisibilité.

 LId  result
 10  <MI:tool xmlns:MI="...">T-85A framing tool</MI:tool>
     <MI:tool xmlns:MI="...">Trim Jig TJ-26</MI:tool>
     <MI:tool xmlns:MI="...">router with a carbide tip 15</MI:tool>
      <MI:tool xmlns:MI="...">Forming Tool FT-15</MI:tool>
 20
 30  <MI:tool xmlns:MI="...">standard debur tool</MI:tool>
 45  <MI:tool xmlns:MI="...">paint harness</MI:tool>
 50
 60

C. Application de la méthode nodes() à l'ensemble de lignes renvoyé par une autre méthode nodes()

Le code suivant interroge les documents XML des instructions de fabrication stockés dans la colonne Instructions de la table ProductModel. La requête renvoie un ensemble de lignes qui contient l'ID du modèle de produit, ainsi que les sites et les étapes de fabrication.

Notez les points suivants :

  • La méthode nodes() est appliquée à la colonne Instructions et renvoie l'ensemble de lignes T1 (Locations). Cet ensemble de lignes contient des copies logiques du document d'origine des instructions de fabrication et indique /root/Location en guise de contexte d'élément.
  • nodes() est appliqué à l'ensemble de lignes T1 (Locations) et renvoie l'ensemble de lignes T2 (steps). Cet ensemble de lignes contient des copies logiques du document d'origine des instructions de fabrication et indique /root/Location/step en guise de contexte d'élément.
SELECT ProductModelID, Locations.value('./@LocationID','int') as LocID,
steps.query('.') as Step       
FROM Production.ProductModel       
CROSS APPLY Instructions.nodes('       
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";       
/MI:root/MI:Location') as T1(Locations)       
CROSS APPLY T1.Locations.nodes('       
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";       
./MI:step ') as T2(steps)       
WHERE ProductModelID=7       
GO

Voici le résultat obtenu :

ProductModelID LocID Step       
----------------------------       
7      10   <step ... />       
7      10   <step ... />       
...       
7      20   <step ... />       
7      20   <step ... />       
7      20   <step ... />       
...

La requête déclare le préfixe MI deux fois. À la place, vous pouvez recourir à WITH XMLNAMESPACES pour déclarer le préfixe une fois et l'utiliser dans la requête :

WITH XMLNAMESPACES (
   'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions'  AS MI)

SELECT ProductModelID, Locations.value('./@LocationID','int') as LocID,
steps.query('.') as Step       
FROM Production.ProductModel       
CROSS APPLY Instructions.nodes('       
/MI:root/MI:Location') as T1(Locations)       
CROSS APPLY T1.Locations.nodes('       
./MI:step ') as T2(steps)       
WHERE ProductModelID=7       
GO

La requête suivante est similaire à la requête précédente, sauf qu'elle applique la méthode exist() au document XML dans l'ensemble de lignes T2(steps) pour extraire uniquement les étapes de fabrication dans lesquelles au moins un outil de fabrication est utilisé. En d'autres termes, l'élément <step> doit posséder au moins un enfant <tool>.

WITH XMLNAMESPACES (
   'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' AS MI)

SELECT ProductModelID, 
       Locations.value('./@LocationID','int') as LocID,
       steps.query('.') as Steps
FROM   Production.ProductModel
CROSS APPLY Instructions.nodes('/MI:root/MI:Location') as T1(Locations)
CROSS APPLY T1.Locations.nodes('./MI:step') as T2(steps)
WHERE  ProductModelID=7
AND    steps.exist('./MI:tool') = 1
GO

Voir aussi

Concepts

Ajout d'espaces de noms à l'aide de WITH XMLNAMESPACES
Type de données xml
Génération d'instances XML
Exemples d'applications XML

Autres ressources

Méthodes des types de données xml

Aide et Informations

Assistance sur SQL Server 2005