SQL Server, avoid encoding special character when using FOR XML PATH

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 

Reference : Tejas Shah (http://www.SQLYoga.com)

Read More

SQLYoga – XML Result sets with SQL Server

Recently, found that people making much complex code to have an XML in application. I have found that they return result set in Table to the application and have applied code to convert that data table to XML by Asp. Net application. When I review the process, I have suggested that why don’t you use the XML feature of the application. They surprised the simple use of it and make their life easy. Let me have sample query to generate XML result sets in SQL Server database. There are two ways to have an XML from query: 1. FOR XML RAW 2. FOR XML PATH Please find following sample queries where both of the way are being used: Lets create sample data DECLARE @SQLYoga TABLE(     ID INT IDENTITY,     Data VARCHAR(50),     CreatedDate DATETIME DEFAULT(GETDATE()),     CreatedBy INT     ) INSERT INTO @SQLYoga(Data) SELECT 'SQLYoga' UNION ALL SELECT 'Tejas Shah' UNION ALL SELECT 'Generate XML' Generate XML with XML ROW SELECT * FROM @SQLYoga FOR XML RAW, ROOT('Records') XML RAW, returns the XML by each record with row element and all columns as attributes in the XML. Generate XML with XML PATH SELECT    * FROM    @SQLYoga FOR XML PATH('Record'), ROOT('Records') XML PATH, returns the XML by each record with separate element and also columns as element within respected element of the record. That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this. Reference: Tejas Shah (www.SQLYoga.com)

Read More