How Stuff and 'For Xml Path' work in SQL Server?

Question

Table is:

Id	Name
1	aaa
1	bbb
1	ccc
1	ddd
1	eee

Required output:

Id	abc
1	aaa,bbb,ccc,ddd,eee

Query:

SELECT ID,      abc = STUFF(                  (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''                )  FROM temp1 GROUP BY id 

This query is working properly. But I just need the explanation how it works or is there any other or short way to do this.

I am getting very confused to understand this.

Answer 1

Here is how it works:

1. Get XML element string with FOR XML

Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument. For example, if we were to run the following statement:

SELECT ',' + name                FROM temp1               FOR XML PATH ('') 

By passing in a blank string (FOR XML PATH('')), we get the following instead:

,aaa,bbb,ccc,ddd,eee 

2. Remove leading comma with STUFF

The STUFF statement literally "stuffs” one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.

SELECT abc = STUFF((             SELECT ',' + NAME             FROM temp1             FOR XML PATH('')             ), 1, 1, '') FROM temp1 

The parameters of STUFF are:

• The string to be “stuffed” (in our case the full list of name with a leading comma)
• The location to start deleting and inserting characters (1, we’re stuffing into a blank string)
• The number of characters to delete (1, being the leading comma)

So we end up with:

aaa,bbb,ccc,ddd,eee 

3. Join on id to get full list

Next we just join this on the list of id in the temp table, to get a list of IDs with name:

SELECT ID,  abc = STUFF(              (SELECT ',' + name                FROM temp1 t1               WHERE t1.id = t2.id               FOR XML PATH (''))              , 1, 1, '') from temp1 t2 group by id; 

And we have our result:

Id	Name
1	aaa,bbb,ccc,ddd,eee

Answer 2

This article covers various ways of concatenating strings in SQL, including an improved version of your code which doesn't XML-encode the concatenated values.

SELECT ID, abc = STUFF (     (         SELECT ',' + name         FROM temp1 As T2         -- You only want to combine rows for a single ID here:         WHERE T2.ID = T1.ID         ORDER BY name         FOR XML PATH (''), TYPE     ).value('.', 'varchar(max)') , 1, 1, '') FROM temp1 As T1 GROUP BY id 

To understand what's happening, start with the inner query:

SELECT ',' + name FROM temp1 As T2 WHERE T2.ID = 42 -- Pick a random ID from the table ORDER BY name FOR XML PATH (''), TYPE 

Because you're specifying FOR XML, you'll get a single row containing an XML fragment representing all of the rows.

Because you haven't specified a column alias for the first column, each row would be wrapped in an XML element with the name specified in brackets after the FOR XML PATH. For example, if you had FOR XML PATH ('X'), you'd get an XML document that looked like:

<X>,aaa</X> <X>,bbb</X> ... 

But, since you haven't specified an element name, you just get a list of values:

,aaa,bbb,... 

The .value('.', 'varchar(max)') simply retrieves the value from the resulting XML fragment, without XML-encoding any "special" characters. You now have a string that looks like:

',aaa,bbb,...' 

The STUFF function then removes the leading comma, giving you a final result that looks like:

'aaa,bbb,...' 

It looks quite confusing at first glance, but it does tend to perform quite well compared to some of the other options.