Wednesday, November 19, 2008

xml data encoding issue in sql server 2005

Today I found one of my friend is struggling with saving xml to sql server 2005 database with Norwegian characters. When he try to insert xml data error is throwing saying "invalid characters found in line number ....". According to the definitions sql server keeps xml data using utf-16 encoding. so this should support Norwegian characters. The xml document which we are trying to save contains the declarations as

<?xml version="1.0" encoding="utf-8" ?>

If we remove the above xml declaration from the xml document before sending it to sql server, it will be automatically handled by the server using utf-16. you can use .Net to remove the declaration before sending it to server and insert declaration while you retrieve xml from database.

No comments: