I attempt to insert a xml in string format to a SQL2005 xml data type column. I receive the error: “XML parsing: line 1, character 39, unable to switch the encoding”. Explicitly specifiy the encoding in UTF-8 does not work out as XML data is stored using Unicode (UTF-16). Removing the encoding property in the xml header solve the issue.
SQL Server 2005 encodes XML data using Unicode (UTF-16). Data in a column of type xml is stored in an internal format as large binary objects (BLOBs) in order to support XML model characteristics, such as document order and recursive structures. Therefore, XML data retrieved from the server comes out in UTF-16; if you want a different encoding for data retrieval, your application must perform the necessary conversion on the retrieved UTF-16 data