Unable to insert xml content using "utf-8" encoding

[Sagar]

I am trying ot insert data to an XML column (MS SQL 2005) using MS sql driver
(Microsoft SQL Server 2005 JDBC Driver 1.1). Please find below the code for
your reference

PreparedStatement pstmt = dbCon.prepareStatement("INSERT INTO
xml_test_table(xml_col, varchar_col) VALUES (?, ?)");
pstmt.setString(1, "<?xml version=\"1.0\" encoding=\"utf-8\"
standalone=\"no\"?><a>aab</a>");
pstmt.setString(2, "sagar");

Note that xml doucment encoding is "UTF-8". When I try to execute above code
I am getting exception as given below.

com.microsoft.sqlserver.jdbc.SQLServerException: XML parsing: line 1,
character 54, unable to switch the encoding
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown
Source)

I believe the default xml encoding is "UTF-8", so it should work fine. If I
remove the encoding part from XML it works fine. If I change it to "UTF-16"
it also work fine. Exception comes only for "UTF-8" encoding.

Is it bug in JDBC driver? Any idea?

I would appreciate if anyone replies to this.

Thanks,
Sagar

[Yesim Koman]

Hi Sagar,

I was able to repro your problem. However, this is a SQL Server 2005
behavior and is not caused by the JDBC driver. Basically, when you leave
the encoding as UTF-8 as in your repro, the server treats your data as
String and throws the exception. If you remove the UTF-8 characters from
your data (visualized as '?'), and then try running your repro you will see
that the xml column will store the entire string as opposed to the body of
your xml.

Unfortunately, I don't have a better answer than what you have already
proposed. You can either leave out the encoding entirely or change it to
UTF-16. Note that SQL Server 2005 always stores and returns xml data as
UTF-16 no matter what the encoding. Hence, either way you would need to
reconvert your data to UTF-8 when you retrieve it from the server.

Hope this helps,
Yesim [MSFT]