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:
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):
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:
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.
Hopefully this will be corrected in newer versions of CF
Till next time...
--Dave
