SQL Server XML (FOR XML PATH)
SQL Server XML (FOR XML PATH) is a SQL statement in the SQL Server Specific category. SQL Server: FOR XML PATH generates XML from query results. Often used for CSV string concatenation. The syntax is SELECT ... FOR XML PATH('element'), ROOT('root');. It returns xML result. A typical example: -- XML output: SELECT name, salary FROM employees WHERE department = 'IT' FOR XML PATH('employee'), ROOT('it_department'); -- <it_department> -- <employee><name>Alice</name><salary>75000</salary></employee> -- <employee><name>Bob</name><salary>65000</salary></employee> -- </it_department> -- CSV hack (STRING_AGG alternative): SELECT STUFF(( SELECT ', ' + name FROM employees WHERE department = 'IT' FOR XML PATH('') ), 1, 2, '') AS employee_list; -- Alice, Bob, Charlie -- Attributes vs elements: SELECT name AS '@name', salary AS '@salary' FROM employees FOR XML… A close relative is OUTPUT Clause, which sQL Server: returns values from DML statements (like PostgreSQL RETURNING). A close relative is STUFF(), which sQL Server: deletes a specified length of characters from a string and inserts another string at the start position.