Systems Engineering and RDBMS

Adding/Replacing Unicode Data in existing XML

Posted by decipherinfosys on June 21, 2010

We have covered Unicode characters, data-types and application changes related to them before in our posts.  Over the weekend, while consuming an XML from another application from the same client company, QA team saw bad data making it’s way to the database.  Upon further research, it was found that the application that was sending the XML was forming that XML by integrating data from several different sources.  It was then inserting those data elements into an existing XML and passing it along when it was done with it.  And in so doing, it was not taking into account the unicode nature of the strings.  Just like you would take care of it when inserting a record into a unicode data-type column, you would do the same thing with the XML as well.  Here is an example:

CREATE TABLE UNICODE_TEST_TBL (COL1 NVARCHAR(100), COL2 VARCHAR(100));

In this table, we have a column called COL1 which is declared to be of Unicode data-type and another one – COL2 which has been declared as a non-Unicode data type column.  Let us try to insert this string into it:

After inserting, this is how the data will look like:

As you can see, by using the prefix of N before the string, we were able to indicate properly to the database engine that the data that is being inserted is a unicode string and we used that for the unicode data type column and hence the data was inserted correctly where as it was corrupted for the non-unicode data type column.  The same thing can be applied for XML as well.

As you can see from above, we first tried to insert the non-unicode string and got corrupted data and then we tried to insert the unicode data-type variable and it appears fine without any issues.  Here is the final XML:

And the same thing applies, if we were replacing (updating) a value in that XML document – example:  Suppose, we want to replace the value of “testing” with the same Unicode string:

Pretty simple fix.  Once the change was made, all the data was perfectly fine.  Doing so at design/development/QA time would have saved a weekend :-)

Resources:

  • MSDN post on XML DML – here and replacing values within a XML document – here.
  • MVP Jacob Sebastian’s post on this topic – here and his entire collection of the XQuery posts – here.
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 84 other followers

%d bloggers like this: