Blog Detail

26 Jun 2015
Tejas Shah
From SQL 2005, we are using FOR XML PATH('') for the string concatenation. I have also mentioned the same in very old blog: Generate Comma Separated List with SELECT statement.  Today, we face an issue when special character gets encoded and we hate to have special character in my string. 

e.g: Table has records like: 
1. Gynecology & Obstetrics 
2. Dermatology 

Expected output (after concatenating two rows): Gynecology & Obstetrics, Dermatology. 

When we used FOR XML PATH, we got the output as: 
Gynecology & Obstetrics,Dermatology ("&" character is encoded and it lists out "&")

This output was not the one which we were expected. To get the expected output, we need to find workaround and we finally get it as: 

SELECT STUFF(((
        SELECT ',' + RTRIM(CONVERT(VARCHAR(50), sm.Speciality))
        FROM Table1 T2
        INNER JOIN Table2 sm ON sm.ID = t2.Speciality_ID
        FOR XML PATH(''),TYPE).value('.[1]', 'varchar(max)')
    ), 1, 1, '')

After adding "TYPE", we are able to get the expected output: Gynecology & Obstetrics, Dermatology 

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.