How to Create Comma-Delimited Lists in SQL Server Using FOR XML PATH?

Use FOR XML PATH to create a comma separated list in SQL Server

This example shows how to use FOR XML PATH to create a comma-separated list from a table:

<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>

Detailed explanation:

The heart of this query is how FOR XML PATH handles the actual XML. Consider a simplified employee table:

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

FOR XML PATH Convert data to XML format. Execute the following query:

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

will generate the following XML:

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

Omitting the 'Employee' element from the PATH clause removes the external XML tag. Modified query:

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

will generate:

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

In the original query, the column alias 'data()' triggered an error when trying to create an invalid XML tag. To solve this problem, correlated subqueries hide errors, strip labels and generate plain text.

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

Finally, REPLACE replaces spaces in the list with commas.

Improved query:

However, the following improved query is preferable:

<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>

This query uses STUFF to remove the first comma and space, avoiding errors when the name contains spaces. Omitting column aliases prevents XML tags from being created.

Other notes:

To escape special XML characters, use .value in combination with 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>

The above is the detailed content of How to Create Comma-Delimited Lists in SQL Server Using FOR XML PATH?. For more information, please follow other related articles on the PHP Chinese website!