What this query does to create comma delimited list SQL Server?

sql, sql-server, sql-server-2008

Solution

The simplest way of explaining it is to look at how `FOR XML PATH` works for actual XML. Imagine a simple table `Employee`:

EmployeeID      Name
1               John Smith
2               Jane Doe

You could use

SELECT  EmployeeID, Name
FROM    emp.Employee
FOR XML PATH ('Employee')

This would create XML as follows

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

Removing the 'Employee' from `PATH` removes the outer xml tags so this query:

SELECT  Name
FROM    Employee
FOR XML PATH ('')

Would create

    <Name>John Smith</Name>
    <Name>Jane Doe</Name>

What you are then doing is not ideal, the column name 'data()' forces an sql error because it is trying to create an xml tag which is not a legal tag, so the following error is generated:

Column name 'Data()' contains an invalid XML identifier as required by FOR XML; '('(0x0028) is the first character at fault.

The correlated subquery hides this error and just generates the XML with no tags:

SELECT  Name AS [Data()]
FROM    Employee
FOR XML PATH ('')

creates

John Smith Jane Doe

You are then replacing spaces with commas, fairly self explanatory...

If I were you I would adapt the query slightly:

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; 

Having no column alias will mean no xml tags are created, and adding the comma within the select query means any names with spaces in will not cause errors,`STUFF` will remove the first comma and space.

ADDENDUM

To elaborate on what KM has said in a comment, as this seems to be getting a few more views, the correct way to escape XML characters would be to use `.value` as follows:

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; 

Problem

Original source

Related problems

• Does T-SQL have an aggregate function to concatenate strings?