How FOR XML PATH('') works when concatenating rows

Question

How does the FOR XML PATH ('') clause do its work when concatenating rows in SQL Server?

I just want an explanation of how the FOR XML PATH ('') clause works...

Creamer

Check this post - codeproject.com/Articles/691102/… – Carlile

It's an abuse. As it's name suggest, the purpose for this feature is XML generation. As it happens, a certain combination of features mean that it can be used to concatenate strings. – Shamanism

@Shamanism Is there a better way in SQL Server? Ie more concise, better in term of performance? – Meperidine

"Is there a better way" - yes - "in SQL Server" - not really. This is string manipulation, it's usually best done in a language with strong string manipulation features. T-SQL isn't one of those. So if possible, move this formatting/presentation logic out of the database. – Shamanism

You have a point there bro. Thanks for the comment. @Shamanism – Creamer

@Shamanism T-SQL can do it, but only in Sybase SQL Anywhere (LIST()). Indeed, many (if not most) implementations of SQL can do it. Recent versions of MySQL (group_concat()), Oracle (listagg()), PostgreSQL (string_agg()), DB2 (listagg()) SQLite (group_concat()), and Firebird (LIST()). In Postgres, Oracle, and I believe DB2 the functions are analytic functions, as well, so they support windowing. An while I agree it should be handled in display by the application, report authoring software is often remarkably inflexible in this regard. – Corley

@BaconBits string_agg exists in SQL Server now – Secessionist

@MartinSmith Oh, snap! I did not see that. One of my biggest pet peeves with SQL Server has finally been resolved. I was quite annoyed when 2016 only included string_split and string_escape. Now I just have to wait until we migrate to SQL Server 2017, which will hopefully be sometime before 2050. – Corley

Answer 1

What FOR XML PATH('xxx') does is create an XML string for the resultset that puts each row in a <xxx></xxx> element and each column value inside the row, in an element with the name for that column.

If the PATH is empty (i.e. PATH('')) it omits the row element in the XML generation. If the column has no name it omits the column element in the XML generation. When both PATH is empty and columns have no names it effectively becomes a string concatenation of all rows.

Run the following statements to get a better insight in the process:

-- Each row is in a <beta></beta> element
-- Each column in that row in a <alfa></alfa> element (the column name)
SELECT
    alfa=','+TABLE_SCHEMA + '.' + TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('beta');

-- Since the PATH is empty, the rows are not put inside an element
-- Each column in that row is in a <alfa></alfa> element (the column name)
SELECT
    alfa=','+TABLE_SCHEMA + '.' + TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

-- Since the PATH is empty, the rows are not put inside an element
-- Since the column has no name it is not put inside an element     
SELECT
    ','+TABLE_SCHEMA + '.' + TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

-- This uses the STUFF function to remove the leading comma to get a proper comma-seperated list    
SELECT STUFF((
    SELECT
        ','+TABLE_SCHEMA + '.' + TABLE_NAME
    FROM
        INFORMATION_SCHEMA.TABLES
    FOR
        XML PATH('')
    ),1,1,''
) AS comma_seperated_list;

---

Now I hear you asking: How can I remove the column name when I simply select a column from a table. There are several ways, in order of my preference:

• XQuery properties: SELECT [text()]=column_name ...
• Use a subquery to select the column value: SELECT (SELECT column_name) ...
• CAST the column to its type: SELECT CAST(column_value AS <TYPE of the column>) ...

Examples:

SELECT
    [text()]=TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

SELECT
    (SELECT TABLE_NAME)
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

SELECT
    CAST(TABLE_NAME AS SYSNAME)
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

Meperidine