Technique for fixing XML parsing: illegal qualified name character

Question

Is there a ultimate solution for fixing the following error:

Msg 9455, Level 16, State 1, Line 8
XML parsing: line 1, character 12, illegal qualified name character

which is raised when converting "illegal" NVARCHAR string to XML.

For example:

DECLARE @Text NVARCHAR(MAX)
SET @Text =  '<tag>' + 'Test <'  + '</tag>' 
SELECT CAST(@Text AS XML)

Can be fixed using CDATA but it is handling few symbols only. In situations like the query below:

DECLARE @Text NVARCHAR(MAX)
SET @Text = '<tag><![CDATA[' + 'Test' + CHAR(28)  + ']]></tag>' -- CHAR(28) --CHAR(8) -- CHAR(29) -- CHAR(63) 
SELECT CAST(@Text AS XML)

it does nothing. Also, it cannot be used in the XML attribute value.

I have try to find a list with all symbols that are breaking XML but I was not able to do. So each time some symbol breaks the XML I am finding it and replacing it, but this is very temporary and hard to maintain solution.

Is there a complete solution for such cases - no matter if it should be done in the application or using CLR function?

Answer 1

It would depend on the XML version but to be on the safer side, one thing you can do is replace all the C0 control ASCII characters in a loop:

DECLARE @counter INT=0
DECLARE @Text NVARCHAR(MAX)
SET @Text = '<tag><![CDATA[' + 'Test' + CHAR(28) + CHAR(55) + CHAR(29) + '<' + ']]></tag>' -- CHAR(28) --CHAR(8) -- CHAR(29) -- CHAR(63) 
WHILE @counter<32
BEGIN
SET @text= REPLACE(@text,CHAR(@counter),'?')
SET @counter=@counter+1
END
SELECT CAST(@Text AS XML)

more info about XML char set:

http://www.w3.org/TR/xml11/#charsets

http://en.wikipedia.org/wiki/Valid_characters_in_XML

http://en.wikipedia.org/wiki/C0_and_C1_control_codes