title: SQL Server: Replace invalid XML characters from a VARCHAR(MAX) field
body: <p>I have a <code>VARCHAR(MAX)</code> field which is being interfaced to an external system in <code>XML</code> format. The following errors were thrown by the interface:</p>
  
  <pre><code>mywebsite.com-2015-0202.xml:413005: parser error : xmlParseCharRef: invalid xmlChar value 29
  ne and Luke's family in Santa Fe. You know you have a standing invitation,&amp;#x1D;
                                                                                 ^
  mywebsite.com-2015-0202.xml:455971: parser error : xmlParseCharRef: invalid xmlChar value 25
  The apprentice nodded, because frankly, who hadn&amp;#x19;t? That diseases like chol
                                                        ^
  mywebsite.com.com-2015-0202.xml:456077: parser error : xmlParseCharRef: invalid xmlChar value 28
  bon mot; a sentimental love of nature and animals; the proverbial British &amp;#x1C;
                                                                                 ^
  mywebsite.com-2015-0202.xml:472073: parser error : xmlParseCharRef: invalid xmlChar value 20
  "And&amp;#x14;you want that?"
            ^
  mywebsite.com-2015-0202.xml:492912: parser error : xmlParseCharRef: invalid xmlChar value 25
  She couldn&amp;#x19;t live like this anymore.
  </code></pre>
  
  <p>We found that the following list of characters are invalid:</p>
  
  <pre><code>&amp;#x0;
  &amp;#x1;
  &amp;#x2;
  &amp;#x3;
  &amp;#x4;
  &amp;#x5;
  &amp;#x6;
  &amp;#x7;
  &amp;#x8;
  &amp;#x9;
  &amp;#xa;
  &amp;#xb;
  &amp;#xc;
  &amp;#xd;
  &amp;#xe;
  &amp;#xf;
  &amp;#x10;
  &amp;#x11;
  &amp;#x12;
  &amp;#x13;
  &amp;#x14;
  &amp;#x15;
  &amp;#x16;
  &amp;#x17;
  &amp;#x18;
  &amp;#x19;
  &amp;#x1a;
  &amp;#x1b;
  &amp;#x1c;
  &amp;#x1d;
  &amp;#x1e;
  &amp;#x1f;
  &amp;#x7f;
  </code></pre>
  
  <p>I am trying to clean this data, and I found a SQL function to clean these characters <a href="http://blogs.technet.com/b/wardpond/archive/2005/07/06/a-solution-for-stripping-invalid-xml-characters-from-varchar-text-data-structures.aspx">here</a>. However, the function was taking <code>NVARCHAR(4000)</code> as input parameter, so I have changed the function to use <code>VARCHAR(MAX)</code> instead. </p>
  
  <p>Could anyone please advise if changing the <code>NVARCHAR(4000)</code> to <code>VARCHAR(MAX)</code> would produce wrong results? Sorry, I wouldn't be able to test this interface locally so thought to seek opinion/advise.</p>
  
  <p>Original Function:</p>
  
  <pre><code>CREATE FUNCTION fnStripLowAscii (@InputString nvarchar(4000))
  RETURNS nvarchar(4000)
  AS
  BEGIN
  IF @InputString IS NOT NULL
  BEGIN
    DECLARE @Counter int, @TestString nvarchar(40)
  
    SET @TestString = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) + ']%'
  
    SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
  
    WHILE @Counter &lt;&gt; 0
    BEGIN
      SELECT @InputString = STUFF(@InputString, @Counter, 1, NCHAR(164))
      SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
    END
  END
  RETURN(@InputString)
  END
  </code></pre>
  
  <p>Modified Version:</p>
  
  <pre><code>CREATE FUNCTION [dbo].RemoveInvalidXMLCharacters (@InputString VARCHAR(MAX))
  RETURNS VARCHAR(MAX)
  AS
  BEGIN
      IF @InputString IS NOT NULL
      BEGIN
        DECLARE @Counter INT, @TestString NVARCHAR(40)
  
        SET @TestString = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) + ']%'
  
        SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
  
        WHILE @Counter &lt;&gt; 0
        BEGIN
          SELECT @InputString = STUFF(@InputString, @Counter, 1, ' ')
          SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
        END
      END
      RETURN(@InputString)
  END
  </code></pre>