Hi,

I know I'm a little late to the table here (no pun intended), but I came across this thread because I was getting the same error message. I figured out the problem in my case, and I think I can add a helpful note here.

If your source is a [text] or [varchar](max) field, and you get the "unable to switch the encoding" error message, that means that the XML text starts with "<?xml version="1.0" encoding="UTF-16" ?>". In this case just cast it to [ntext] or [nvarchar](max). So, relative to Dave's original posting, changing the CONVERT to NVARCHAR()should work:

SELECTCONVERT(XML,CONVERT(NVARCHAR(MAX),xml_field))

FROMsource_table

If your source is an [ntext] or [nvarchar](max) field, and you get the "unable to switch the encoding" error message, that means that the XML text starts with "<?xml version="1.0" encoding="UTF-8" ?>". In this case just cast it to [text] or [varchar](max).

Basically, if the source XML says "UTF-8", and you want/need it to go through a character data type, the data type has to be [varchar] or [text] (both of which use 1 byte, 8 bit, encoding). If the XML says "UTF-16", and you want/need it to go through a character data type, the data type has to be [nvarchar] or [ntext] (both of which use 2 byte, 16 bit, encoding).