Rowset Concatenation with Special Characters

Whenever we think about row set concatenation in SQL Server the first method that comes to our mind is by the use of FOR XML PATH. There are numerous blogs on the method explained in web.
Couple of days back one of my fellow colleagues came to me with an issue with this. He couldn’t use the above method for his scenario the reason being for his scenario the row set represented a comments field which was capture from some file using import process. As such it had lot of special characters inside the field and it was causing code to break when trying to built into XML. As such he couldnt apply above method for concatenating row values. I analyzed it a bit and managed to solve it by tweaking the method a bit. I’m sharing method here to help others who face similar issues.
The scenario can be illustrated by below simplified example
Consider the table below with single varchar field with values as below

Now applying above method lets see what output would be

 select stuff((select ‘,’ + val
from @t

for xml path(”)),1,1,”)

the output looks like below

If you notice the output carefully you can see that XML format will replace some of characters to their encoded values.
So if you want to preserve their original format you need to use small tweak like below

 select stuff((select ‘,’ + val
from @t
for xml path(”),type).value(‘.’,’varchar(max)’),1,1,”)

This is an easy workaround that can be used when you want to apply FOR XML method to generate concatenated value list from a field containing special characters as above.