Technique for fixing XML parsing: illegal qualified name character



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 only 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 attirbute value.


I have try to find a list with all symbols that are braking XMLs 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?


No comments:

Post a Comment