XML Parsing - Illegal XML Character (when Executing Stored Procedure, Running Procedure Queries Results In No Errors)

I have a valid XML document (this has been confirmed using multiple XML validators including online validators and the Sublime Text XML validator plugin). I receive the following e

Solution 1:

Pure guessing:

• The file is utf-8 encoded (or any other encoding, SQL-Server 2008 cannot read natively).
  • You must know, that SQL-Server is rather limited with file encodings. CHAR (or VARCHAR) is extended ASCII 1-byte encoding and NCHAR (or NVARCHAR) is UCS-2 2-byte encoding (which is almost identical with UTF-16).
  • With SQL-Server 2016 (and SP2 for v2014) some further support was introduced, especially for utf-8.
  • Try to open your XML with an appropriate editor (e.g. notepad++) and try to find out the file's encoding. Try to save this as "unicode / UCS-2 / utf-16" and retry the import.
  • Try to use your import with CLOB instead of BLOB. Reading the file as binary LargeObject will take the bytes one after the next. SQL-Server will try to read these bytes as string with fixed size per character. A character LOB might work under special circumstances.
  • Check the first two bytes for a BOM (byte order mark)
• There is some dirt within your XML
  • Open the file with an HEX-editor and try to find strange codes
• Your code processes the file's content within a dynamically created statement.
  • In such cases sometimes you run into truncation or string-breaking quotes
• General hint:
  • If you import data and you expect issues it is highly recommended to use a 2-step-approach
  • Read your file into a tolerant staging table (with NVARCHAR(MAX) or even VARBIANRY(MAX) target columns) and try to continue with this.
  • It might be necessary to use another tool to change your file before the import.