XML in SQL Server 2005

Those of you who are familiar with SQL Server 2005 will know that Microsoft have provided a brand new “xml” datatype for use with it.

The new xml datatype effectively allows a database administrator or developer to create SQL databases which contain heirarchical information within the context of a single field. This is an incredibly powerful data design paradigm. Using the standard data types for relational database management systems, you would have to create at least one normalised table per entity class, with each property of the entity class being designed for - ahead of time - by creating the table schema design for it. This works very well if you know well in advance exactly what data you are planning to store.

But what if you have to store information on a product which has a vast quantity of varying properties (such a car). You couldn’t add these as properties of a table, since you would quickly run out of space or have a lot of unused, redundant fields.

Traditionally, this would be exposed as a design flaw later on in the project lifecycle, necessitating the addition of lots of child tables with foreign keys, and therefore a large amount of code would need to be changed in order to accomodate this.

Enter, XML. Already used as the basis for most modern day information storage and transfer needs, it is now possible to store XML strings directly into columns within your SQL database. This is supported in any .mdf database engine, including SQL Server, SQL Everywhere, SQL Express and SQL Mobile.

This is wonderful news for object orientated programmers, who now have a flexible way of storing rich, hierarchical information, which can be extended at a moment’s notice without requiring any breaking changes to be made.

The XML in the database can also be queried. Previously to the XML datatype, I have seen XML data were stored as ntext (eww), varchar (yuck) or BLOB’s (binary objects). The new XML datatype can be queried directly. This is going to be as close as relational databases come to pivot-tables

Last week, in a recent database implementation for one of my clients, I noticed that SQL Server 2005 was throwing some very odd errors back to my SQL data class: “SqlException: unable to switch the encoding”. Long story short, SQL Server 2005 does not support UTF-8 to store data, and the “standard” XML well formed declaration contains encode=”UTF-8″.

Nearly everyone starting out using the xml datatype will hit this error. The error will be thrown the moment that you attempt to insert sample XML into the column:

System.Data.SqlClient.SqlException:
“XML parsing: line 1, character xxx, unable to switch the encoding”

SQL Server 2005 expects to receive UTF-16 encoded XML, and will not accept XML if it contains a UTF-8 declaration.

The solutions are to either:

1. Remove the declaration from your XML when inserting to your SQL database
2. Change the encoding to UTF-8.

If you want to do change the encoding to UTF-16, you could try using the following code to perform your conversion:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
System.Xml.XmlDocument newDoc = new System.Xml.XmlDocument();
System.IO.StreamReader r = new System.IO.StreamReader(”C:\XML.xml”);
doc.LoadXml(r.ReadToEnd());
r.Close();

System.Xml.XmlDeclaration xmldecl;
System.Xml.XmlElement root = newDoc.DocumentElement;
xmldecl = newDoc.CreateXmlDeclaration(”1.0″, null, null);
xmldecl.Standalone = “yes”;xmldecl.Encoding = “UTF-16″;
newDoc.LoadXml(doc.DocumentElement.OuterXml.ToString());
newDoc.InsertBefore(xmldecl, root);

Or if you’re in a hurry (and have a hard-coded declaration string):

xmlString = xmlString.replace(”<?xml version=”"1.0″” encoding=”"UTF-8″“?>“,”");

I hope this helps someone. If you find the code above useful, please leave a comment.

---

Other Related Posts (that you might like)

Microsoft: Please allow Null values to be returned from strongly typed datasets!

An interesting thought experiment (9 days to go)

Houston, we have sponsorship. (24 days to go)

Software Monitor Taking Shape (15 days to go)

---