XML data type encoding

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.

<?xml version=”1.0″ encoding=”UTF-8″ ?>

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

Have a read on the International Features is MS SQL 2005 and Limitations of the xml Data Type

Reference: MSDN

Leave a Reply

Your email address will not be published. Required fields are marked *