Recently I was working on some SSIS reports. Everything was working fine up till my queries ran on testing server where it failed during parsing of special characters in the query.
Below query worked fine when there was no special character in the data.
DECLARE @t TABLE ( StudentID INT, Subjects VARCHAR(8000) ) INSERT INTO @T VALUES (111,'English French,Maths,Science'), (112,'English Spanish,English,Science') SELECT StudentId 'Student Id', LTRIM(RTRIM(m.n.value('.','varchar(8000)'))) AS 'Subject Name' FROM ( SELECT StudentID, CAST('' + REPLACE(Subjects,',','') + '' AS XML) AS node FROM @t )XmlTable CROSS APPLY node.nodes('/XMLRoot/RowData')m(n)
It started failing as soon as I got special characters in my data.
--(111,'English & French,Maths,Science'), --(112,'English & Spanish,English,Science') Msg 9421, Level 16, State 1, Line 13 XML parsing: line 1, character 28, illegal name character
This is what I made changes to my query to get the desired results. Here I am using
(SELECT ... FOR XML PATH()) instead of the naked Subjects. I am letting
FOR XML PATH() do all the hard work for me.
--Changed below line CAST('' + REPLACE(Subjects,',','') + '' AS XML) AS node --with CAST('' + REPLACE((SELECT Subjects AS [*] FOR XML PATH('')),',','') + '' AS XML) AS node
We can see now we are getting the results with “&” characters.
I hope this will help someone and will save some precious time.