SQL – How to decode HTML entities when using FOR XML PATH

by

When building complicated SQL statements often I will make use of a SELECT – FOR XML PATH statement to concatenate many results into a single string.

For example

SELECT STUFF( (SELECT ',' + y
 FROM dbo.x FOR XML PATH('') ), 1, 1, '')

But this builds the string using XML, leaving special characters using the HTML codes.

E.g. an ampersand (&) would be seen as &

To avoid seeing the HTML entities you need change the query, adding ‘type’ and and specifying the type ‘.value(‘.[1]’,nvarchar(max)’)’.

For example

SELECT STUFF( (SELECT ',' + y
 FROM dbo.x FOR XML PATH, TYPE).value('.[1]','nvarchar(max)' ), 1, 1, '')

Reference: http://stackoverflow.com/questions/15643683/how-do-i-avoid-character-encoding-when-using-for-xml-path