If you ever find yourself trying to get XML data out of a varchar field, you may get this error.
XML parsing: line 1, character 1055, illegal xml character
You could use this:
SELECT CAST(CAST( REPLACE( packet, 'UTF-8', 'UTF-16' ) AS NVARCHAR(MAX)) AS XML) FROM DataTable
I just had these characters that were outside of the utf-8 definition. So when SQL when to convert it, it was not able. Just use the SQL trickery above to pretend that it is stored as utf-16. In the future, my company just needs store it correctly.
’ curly closing quote
“ double curly quote
” double closing curly quote
– long dash
• bullet point