I ran into an issue recently while trying to insert XML data into a MS SQL 200 DB. The XML being inserted contained Cyrillic characters. The DB type was setup as XML and the stored procedure was also setup with the var set as XML so it should store the data without issue. The XML data type can store this type of data without issue according to the MS SQL docs. However, the Cyrillic data in the XML being inserted was shown in the DB as "?????".

So, remembering work I did prior when dealing with this type of data I changed the proc call.

Original call:

view plain print about
1<CFQUERY name="XXXXX" datasource="XXXX">
2    xmlData_INS
3    @item_id = 99999,
4    @user_id = 99999,
5    @xml_data = '#theXML#'
6</CFQUERY>

Updated call (notice the "N" before the data):

view plain print about
1<CFQUERY name="XXXXX" datasource="XXXX">
2    xmlData_INS
3    @item_id = 99999,
4    @user_id = 99999,
5    @xml_data = N'#theXML#'
6</CFQUERY>

In making this change I then started getting a CF error:

view plain print about
1Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]XML parsing: line 1, character 38, unable to switch the encoding

This is where I slammed my head against my desk. I then checked out the encoding of the XML and found something interesting. The XML source was from Adobe Acrobat and was posted in UTF-16 encoding. However, XMLParse() changed the encoding to UTF-8.

While investigating a solution I came across this blog entry from Jared Rypka-Hauers's blog: http://www.web-relevant.com/blogs/cfobjective/index.cfm?mode=entry&entry=05CA3562-BDB9-5320-E197F12577FB92ED which was just about the same issue I was having except there was no db insert.

Using what I learned from Jared's blog entry; I added this piece of code and the XML was then inserted correctly. I also had to keep the "N" in the stored procedure call so that CF passed the data to the database correctly.

view plain print about
1<CFSET theXML = replace(theXML,'UTF-8','UTF-16')>

Hopefully this will be corrected in newer versions of CF

Till next time...

--Dave