title: Technique for fixing XML parsing: illegal qualified name character
body: <p>Is there a ultimate solution for fixing the following error:</p>
  
  <blockquote>
    <p>Msg 9455, Level 16, State 1, Line 8<br>
    XML parsing: line 1, character 12, illegal qualified name character</p>
  </blockquote>
  
  <p>which is raised when converting "illegal" <code>NVARCHAR</code> string to <code>XML</code>. </p>
  
  <p>For example:</p>
  
  <pre><code>DECLARE @Text NVARCHAR(MAX)
  SET @Text =  '&lt;tag&gt;' + 'Test &lt;'  + '&lt;/tag&gt;' 
  SELECT CAST(@Text AS XML)
  </code></pre>
  
  <p>Can be fixed using <code>CDATA</code> but it is handling few symbols only. In situations like the query below:</p>
  
  <pre><code>DECLARE @Text NVARCHAR(MAX)
  SET @Text = '&lt;tag&gt;&lt;![CDATA[' + 'Test' + CHAR(28)  + ']]&gt;&lt;/tag&gt;' -- CHAR(28) --CHAR(8) -- CHAR(29) -- CHAR(63) 
  SELECT CAST(@Text AS XML)
  </code></pre>
  
  <p>it does nothing. Also, it cannot be used in the XML attribute value.</p>
  
  <p>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.</p>
  
  <p>Is there a complete solution for such cases - no matter if it should be done in the application or using CLR function? </p>