Saturday, March 8, 2014

Creating concatenated delimited string from a SQL result set and avoid character encoding when using “FOR XML PATH”

There are a number of ways of getting a column result set into a single delimited string. If it is a function then you can use single variable assigning method or STUFF tsql function. The STUFF function in conjunction with FOR XML PATH code is the most easy usage if you have to use as an inner query. But it automatically html encodes string in certain cases (see case 2 bad example) .

Lets look at a simple exaple of  number of country names

Case 1. Variable based solution 
declare @delimitedCountryName varchar(max)
set @delimitedCountryName=''
select @delimitedCountryName+=case when len(@delimitedCountryName) > 0 then +','+ CountryName else CountryName end
FROM Country

select @delimitedCountryName

Case 2. The XML path and stuff system function based solution
//BAD: because if country name contains special characters then it will be automatically html encoded (e.g. if country name is 'papua & new guinea' it will be shown as    'papua & new guinea' 
select stuff(
(select ', ' + countryname
from Country
for xml path('')
)
, 1, 1, '') as delimitedCountryName;


// GOOD approach (either use case 1 or below option)
select stuff(
(select ', ' + countryname
from Country
for xml path(''), root('MyString'), type
).value('/MyString[1]','varchar(max)')

No comments:

Post a Comment