One common use for the
FOR XML function is to concatenate the values of multiple rows.
Here's an example using the Customers table:
SELECT STUFF( (SELECT ';' + Email FROM Customers where (Email is not null and Email <> '') ORDER BY Email ASC FOR XML PATH('')), 1, 1, '')
In the example above,
FOR XML PATH('')) is being used to concatenate email addresses, using
; as the delimiter character. Also, the purpose of
STUFF is to remove the leading
; from the concatenated string.
STUFF is also implicitly casting the concatenated string from XML to varchar.
Note: the result from the above example will be XML-encoded, meaning it will replace
< characters with
< etc. If you don't want this, change
FOR XML PATH('')) to
FOR XML PATH, TYPE).value('.','varchar(MAX)'), e.g.:
SELECT STUFF( (SELECT ';' + Email FROM Customers where (Email is not null and Email <> '') ORDER BY Email ASC FOR XML PATH, TYPE).value('.','varchar(900)'), 1, 1, '')
This can be used to achieve a result similar to
GROUP_CONCAT in MySQL or
string_agg in PostgreSQL 9.0+, although we use subqueries instead of GROUP BY aggregates. (As an alternative, you can install a user-defined aggregate such as this one if you're looking for functionality closer to that of