Comment créer des listes délimitées par des virgules dans SQL Server à l'aide de FOR XML PATH ?

Utilisez FOR XML PATH pour créer une liste séparée par des virgules dans SQL Server

Cet exemple montre comment utiliser FOR XML PATH pour créer une liste séparée par des virgules à partir d'un tableau :

<code class="language-sql">SELECT 
    E1.deptno, 
    allemp = Replace ((SELECT E2.ename AS 'data()' 
                       FROM emp AS e2 
                       WHERE e1.deptno = e2.DEPTNO 
                       FOR xml PATH('')), ' ', ', ') 
 FROM EMP AS e1 
 GROUP BY DEPTNO; </code>

Explication détaillée :

Le cœur de cette requête est de savoir comment FOR XML PATH gère le XML réel. Considérons un tableau d'employés simplifié :

<code>EmployeeID      Name
1               John Smith
2               Jane Doe</code>

FOR XML PATH Convertissez les données au format XML. Exécutez la requête suivante :

<code class="language-sql">SELECT  EmployeeID, Name
FROM    emp.Employee
FOR XML PATH ('Employee')</code>

générera le XML suivant :

<code class="language-xml"><employee><employeeid>1</employeeid><name>John Smith</name></employee><employee><employeeid>2</employeeid><name>Jane Doe</name></employee></code>

L'omission de l'élément 'Employee' de la clause PATH supprime la balise XML externe. Requête modifiée :

<code class="language-sql">SELECT  Name
FROM    Employee
FOR XML PATH ('')</code>

générera :

<code class="language-xml"><name>John Smith</name><name>Jane Doe</name></code>

Dans la requête d'origine, l'alias de colonne 'data()' a déclenché une erreur lors de la tentative de création d'une balise XML non valide. Pour résoudre ce problème, les sous-requêtes corrélées masquent les erreurs, suppriment les étiquettes et génèrent du texte brut.

<code class="language-sql">SELECT  Name AS [Data()]
FROM    Employee
FOR XML PATH ('')</code>

Enfin, REPLACE remplace les espaces dans la liste par des virgules.

Requête améliorée :

Cependant, la requête améliorée suivante est préférable :

<code class="language-sql">SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH('')
            ), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO; </code>

Cette requête utilise STUFF pour supprimer la première virgule et l'espace, évitant ainsi les erreurs lorsque le nom contient des espaces. L'omission des alias de colonne empêche la création de balises XML.

Autres notes :

Pour échapper aux caractères XML spéciaux, utilisez .value en combinaison avec FOR XML PATH :

<code class="language-sql">SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO; </code>

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!