SQL Concatenation with the STUFF function and XML PATH clause

Recently I had the necessity to retrieve a concatenated string of values as a column for a report.  This can be achieved quite simply by creating a UDF that returns the concatenated string and calling within the SELECT statement.

However, I do not like using UDF’s within SELECT statements due to performance issues.  So I started searching the net for a possible alternative and came across the following:

http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx

Now I have never used the SQL STUFF function or XML Path clause but the combination of the two enabled me to achieve my goal very quickly.

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.

You can execute SQL queries to return results as XML instead of standard rowsets. These queries can be executed directly or executed from within stored procedures and user-defined functions. To retrieve the results directly, first use the FOR XML clause of the SELECT statement.

SELECT TOP 1
  STUFF(
            (
                SELECT ', ' + Username
                FROM Users
                FOR XML PATH('')
            ), 1, 1, ''
        ) As ListOfUsers
From Users