What is FOR XML PATH('') used for in SQL Server?

Prior to SQL Server 2017 introducing STRING_AGG(), FOR XML PATH('') paired with the STUFF() function was the universal standard technique in T-SQL to aggregate multiple row values into a single comma-delimited string per group.

Why is .value('.', 'NVARCHAR(MAX)') necessary with FOR XML PATH?

Using FOR XML PATH directly converts special characters like ampersands (&), less-than ( ) into XML entities (&, <, >). Appending .value('.', 'NVARCHAR(MAX)') unescapes these entities back to original plain text.

How does this compare to STRING_AGG()?

STRING_AGG() was added in SQL Server 2017. For databases running on SQL Server 2008, 2012, 2014, or 2016, FOR XML PATH remains the only supported native string aggregation method.

SQL Server FOR XML PATH Generator: String Concatenation & XML Query Builder

The Standard SQL Server String Aggregation Solution

Prior to SQL Server 2017, Microsoft SQL Server lacked a native GROUP_CONCAT() function like MySQL or STRING_AGG() like PostgreSQL. Developers worldwide relied on FOR XML PATH('') combined with STUFF() to concatenate multiple row values into a single delimited string.

Anatomy of the STUFF + FOR XML PATH Query

FOR XML PATH(''): Formats each row as XML elements with an empty tag wrapper, concatenating all values into a single stream.
STUFF(string, 1, len, ''): Removes the leading comma or delimiter from the beginning of the concatenated string.
.value('.', 'NVARCHAR(MAX)'): Converts the raw XML data back to an NVARCHAR string, unescaping XML entities like & into &.

Frequently Asked Questions

When should I use STRING_AGG() instead?

If you are running SQL Server 2017 or later (or Azure SQL Database), STRING_AGG(ColumnName, ', ') is cleaner and faster. For backwards compatibility with SQL Server 2008–2016, FOR XML PATH('') is mandatory.