Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Why STUFF + FOR XML PATH:** SQL Server pattern for string aggregation pre-2017. Builds comma-separated lists, XML fragments. Replaced by STRING_AGG in modern SQL. **Syntax:** STUFF(string, start, length, replace)—replaces chars. Combined with FOR XML PATH(''): concatenates...
Why STUFF + FOR XML PATH: SQL Server pattern for string aggregation pre-2017. Builds comma-separated lists, XML fragments. Replaced by STRING_AGG in modern SQL.
Syntax: STUFF(string, start, length, replace)—replaces chars. Combined with FOR XML PATH(''): concatenates rows into string; STUFF removes leading comma.
Scalability: FOR XML PATH scans all rows—O(n). For large aggregations (100K+ rows per group), consider CLR aggregate or application-layer concatenation. STRING_AGG is native, optimized.
SELECT STUFF((SELECT ','+name FROM t FOR XML PATH('')),1,1,'')
Red Flag: Using FOR XML without escaping (</ breaks XML). Pro-Move: 'STRING_AGG in SQL Server 2017+—simpler and faster; we migrated legacy reports.'
According to DataEngPrep.tech, this is one of the most frequently asked Python/Coding interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.