In my last post I mentioned that SQL Server 2005 provides with you with the flexibility of having an XML document stored into one single column of “Xml” datatype. Just like a int, datetime or a nvarchar() datatype, you can define “Xml” datatype to store the whole Xml document or a fragment as long as your XML is valid.
But the catch here is if your XML file has a header something like “ “, and you are sending this document as a string to the database, you are in trouble. You are presented with an exception something like “XML parsing: line 1, character 38, unable to switch the encoding”.
So the solution is, while you are opening the FileStream, open with a second parameter “Encoding.Unicode”. That's it. So now your XML document is going to be stored in Unicode (UTF-16) format. However when you retrieve it back from database, remember to convert it to UTF-8, if you are expecting it.
It pays to use http://groups.google.com
Check out limitations with “XML datatype“ in SQL server 2005
Update: Or better still, you are better off using the following.
m_sqlCommand.Parameters[
"@XmlDocument"].Value = new SqlXml(new XmlTextReader(xmlDocumentToPush, XmlNodeType.Document, null));