Using FOR XML PATH(”) for String Concatenation

FOR XML PATH

Every now and then  I need to concatenate a column of strings returned from a SQL query, often I use the COALESCE function,

COALESCE





....but lately I've been using FOR XML PATH(”) a lot and am finding it easier to use. FOR XML PATH function can also be used for string concatenation when generating XML:

STUFF FOR XML PATH
Another handy function for string manipulation is STUFF. The STUFF function inserts a string

into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.


STUFF (character_expression , start , length ,character_expression)

So applying that to the code above , we get something like this :