title: How can I replace all XML escape sequences with the unicode in SQL Server nvarchar?
body: <p>I'm using SQL Server 2012, and I have a nvarchar that has XML-escaped representations of UTF characters because they are present in a filename (most notably <code>\uFFFF</code>).</p>
  
  <p>How can I run through a nvarchar and replace all these occurrences with their actual Unicode characters?</p>
  
  <p>This works, but I'd have to enumerate each one, and this is not really feasible (Invalid: <code>[#x0-#x8]|#xB|#xC|[#xE-#x1F]|[#x7F-#x84]|[#x86-#x9F]|[#xD800-#xDFFF]|[#xFDD0-#xFDEF]|#xFFFE|#xFFFF|[#x10FFFF-Up]</code>)</p>
  
  <pre><code>DECLARE @string nvarchar(2000)
  SET @string = N'&amp;#xFFFF;&amp;#65535;'
  SELECT @string,
  REPLACE(REPLACE(REPLACE(
  REPLACE(REPLACE(REPLACE(
      @string, 
  '&amp;#xFFFF;', NCHAR(0xFFFF)), '&amp;#xFFFE;', NCHAR(0xFFFE)), '&amp;#x0000;', NCHAR(0x0000)),
  '&amp;#65535;', NCHAR(65535)), '&amp;#65534;', NCHAR(65534)), '&amp;#0;', NCHAR(0))
  </code></pre>
  
  <p>How can I replace all of them at once? (&amp;#x******; => NCHAR(0x******) and &amp;#******; => NCHAR(******)), ideally without a CLR function.</p>