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

Solution 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('');