Microsoft SQL Server
POUR XML PATH

.NET Framework ASP.NET C# Language Entity Framework Java Language MySQL Oracle Database PHP SQL Visual Basic .NET Language
Recherche…


Remarques

Il existe également plusieurs autres modes FOR XML :

  • FOR XML RAW - Crée un élément <row> par ligne.
  • FOR XML AUTO - Tente de générer automatiquement une hiérarchie.
  • FOR XML EXPLICIT - Fournit plus de contrôle sur la forme du XML, mais est plus encombrant que FOR XML PATH .

Bonjour le monde XML

SELECT 'Hello World' FOR XML PATH('example')

<example>Hello World</example>

Spécifier les espaces de noms

SQL Server 2008 
WITH XMLNAMESPACES (
    DEFAULT 'http://www.w3.org/2000/svg',
    'http://www.w3.org/1999/xlink' AS xlink
)
SELECT 
    'example.jpg' AS 'image/@xlink:href',
    '50px' AS 'image/@width',
    '50px' AS 'image/@height'
FOR XML PATH('svg')

<svg xmlns:xlink="http://www.w3.org/1999/xlink" xmlns="http://www.w3.org/2000/svg">
    <image xlink:href="firefox.jpg" width="50px" height="50px"/>
</svg>

Spécification de la structure à l'aide d'expressions XPath

SELECT
    'XPath example' AS 'head/title',
    'This example demonstrates ' AS 'body/p',
    'https://www.w3.org/TR/xpath/' AS 'body/p/a/@href',
    'XPath expressions' AS 'body/p/a'
FOR XML PATH('html')

<html>
    <head>
        <title>XPath example</title>
    </head>
    <body>
        <p>This example demonstrates <a href="https://www.w3.org/TR/xpath/">XPath expressions</a></p>
    </body>
</html>

Dans FOR XML PATH , les colonnes sans nom deviennent des nœuds de texte. NULL ou '' devient donc des nœuds de texte vides. Remarque: vous pouvez convertir une colonne nommée en une colonne non nommée en utilisant AS * 

DECLARE @tempTable TABLE (Ref INT, Des NVARCHAR(100), Qty INT)
INSERT INTO @tempTable VALUES (100001, 'Normal', 1), (100002, 'Foobar', 1), (100003, 'Hello World', 2)

SELECT ROW_NUMBER() OVER (ORDER BY Ref) AS '@NUM',
     'REF' AS 'FLD/@NAME', REF AS 'FLD', '',
     'DES' AS 'FLD/@NAME', DES AS 'FLD', '',
     'QTY' AS 'FLD/@NAME', QTY AS 'FLD'
FROM @tempTable 
FOR XML PATH('LIN'), ROOT('row')

<row>
  <LIN NUM="1">
    <FLD NAME="REF">100001</FLD>
    <FLD NAME="DES">Normal</FLD>
    <FLD NAME="QTY">1</FLD>
  </LIN>
  <LIN NUM="2">
    <FLD NAME="REF">100002</FLD>
    <FLD NAME="DES">Foobar</FLD>
    <FLD NAME="QTY">1</FLD>
  </LIN>
  <LIN NUM="3">
    <FLD NAME="REF">100003</FLD>
    <FLD NAME="DES">Hello World</FLD>
    <FLD NAME="QTY">2</FLD>
  </LIN>
</row>

L'utilisation de nœuds de texte (vides) permet de séparer le nœud de sortie précédent du nœud suivant, afin que SQL Server sache démarrer un nouvel élément pour la colonne suivante. Sinon, cela devient confus lorsque l'attribut existe déjà sur ce qu'il pense être l'élément "actuel".

Par exemple, sans les chaînes vides entre l'élément et l'attribut dans l' SELECT , SQL Server génère une erreur:

La colonne centrée sur les attributs 'FLD / @ NAME' ne doit pas figurer après un frère non centré sur les attributs dans la hiérarchie XML de FOR XML PATH.

Notez également que cet exemple a également inclus le XML dans un élément racine nommé row , spécifié par ROOT('row')

Utilisation de FOR XML PATH pour concaténer des valeurs

Le FOR XML PATH peut être utilisé pour concaténer des valeurs dans string. L'exemple ci-dessous concatène des valeurs dans une chaîne CSV : 

DECLARE @DataSource TABLE
(
    [rowID] TINYINT
   ,[FirstName] NVARCHAR(32)
);

INSERT INTO @DataSource ([rowID], [FirstName])
VALUES (1, 'Alex')
      ,(2, 'Peter')
      ,(3, 'Alexsandyr')
      ,(4, 'George');

SELECT STUFF
(
    (
        SELECT ',' + [FirstName]
        FROM @DataSource
        ORDER BY [rowID] DESC
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,1
    ,''
);

Quelques notes importantes:

  • la clause ORDER BY peut être utilisée pour ordonner les valeurs de manière préférée
  • Si une valeur plus longue est utilisée comme séparateur de concaténation, le paramètre de fonction STUFF doit également être modifié. 
SELECT STUFF
(
    (
        SELECT '---' + [FirstName]
        FROM @DataSource
        ORDER BY [rowID] DESC
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,3 -- the "3" could also be represented as: LEN('---') for clarity
    ,''
);
  • comme l'option TYPE et la fonction .value sont utilisées, la concaténation fonctionne avec la NVARCHAR(MAX)


Modified text is an extract of the original Stack Overflow Documentation
Sous licence CC BY-SA 3.0
Non affilié à Stack Overflow