SQL Tips: Parsing special characters in Queries

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('.[1]','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)

Output:

Output5.JPG

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

Output:

output6

We can see now we are getting the results with “&” characters.

I hope this will help someone and will save some precious time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s