This may get me in trouble with certain people, but I have a confession to make. I like storing XML in my database. More specifically, I like using SQL-XML (e.g. ISO/IEC 9075-14:2003) in SQL Server. For those of you who may take offense, let me add a few caveats and qualifications first:
- Storing XML in a database should NEVER be done to avoid proper database design. But lets face it, if you are dealing with highly dynamic structures, or the data is normally in XML form, then is seems ok to me.
- If you are going to store XML in a database use a data type built to make sense of the XML. That means Text, Memo, and/or (N)Varchar data types are not suitable, use the 'xml' data type. By using a proper type you can query your database using XQuery, XPath, etc...
If you are a database professional, you may be thinking 'Well... that might be ok.'. This is where I am sure I will lose you, because I also like using an ORM for database persistance. Now there... you see... you DB guys are now shouting at the screen. On this point, I make no appologies. As true with most technologies there are times where tools like this make a lot of sense and times when they don't. For now, lets just assume this is a case where it makes sense.
I like using NHibernate, but when it comes to working with XML and SQL Server you have to do a little extra work on your own. The reason for this relates to the absense of a fully adopted database "standard" related to persistance of XML/DOM structures.
With all that out of the way lets get down to what is really needed. If you use NHibernate you might have noticed there is no mapping Type for XML so we need to make our own. This is pretty easy, here is an example:
using System;
using System.Data;
using NHibernate.SqlTypes;
using NHibernate.UserTypes;
public sealed class XmlDataType : IUserType
{
private static readonly SqlType[] _sqlTypes = new SqlType[]
{
new StringClobSqlType()
};
#region IUserType Members
public object NullSafeGet(IDataReader rs, string[] names, object owner)
{
object value = rs.GetValue(rs.GetOrdinal(names[0]));
if (value == DBNull.Value)
return null;
var text = (string)value;
var data = new PersistableXml { String = text };
return data;
}
public void NullSafeSet(IDbCommand cmd, object value, int index)
{
if (value != null)
{
string str = ((PersistableXml)value).String;
if (!String.IsNullOrEmpty(str) && str.Length > 0)
{
((IDataParameter)cmd.Parameters[index]).Value = str;
return;
}
}
((IDataParameter)cmd.Parameters[index]).Value = DBNull.Value;
}
public object DeepCopy(object value)
{
if (value == null) return null;
var data = (PersistableXml)value;
var copy = new PersistableXml(data.NamespaceManager)
{
String = data.String
};
return copy;
}
public SqlType[] SqlTypes
{
get { return _sqlTypes; }
}
public Type ReturnedType
{
get { return typeof(PersistableXml); }
}
public bool IsMutable
{
get { return true; }
}
bool IUserType.Equals(object x, object y)
{
if (x == null && y == null)
// Both of them are null.
return true;
var d1 = x as PersistableXml;
var d2 = y as PersistableXml;
if (d1 == null || d2 == null || String.IsNullOrEmpty(d1.String) || String.IsNullOrEmpty(d2.String))
// 1. Both are XmlData, but not both null.
// 2. One of given files is not of the right type.
return false;
return d1.String.CompareTo(d2.String) == 0;
}
public object Assemble(object cached, object owner)
{
return DeepCopy(cached);
}
public object Disassemble(object value)
{
return DeepCopy(value);
}
public int GetHashCode(object x)
{
return GetHashCode();
}
public object Replace(object original, object target, object owner)
{
throw new NotImplementedException();
}
#endregion
}
If you look closely at the above code you will notice that it uses a Type named PersistableXml. This is essentially a bridge to convert XML structures like IXPathNavigable and XMLDocument to something our UserType can hand off to NHibernate. This is the Type that will be used by your application layers, mapped class files, etc. Here is my implementation:
using System;
using System.Xml;
using System.Xml.Serialization;
using System.Xml.XPath;
[Serializable]
public sealed class PersistableXml
{
private string _stringData;
public PersistableXml()
{
}
public PersistableXml(IXPathNavigable xml)
{
Doc = xml;
if (xml == null || ((XmlDocument)Doc).DocumentElement == null)
_stringData = string.Empty;
else
_stringData = ((XmlDocument)Doc).DocumentElement.OuterXml;
}
public PersistableXml(string xml)
: this(xml.ToNavigableXml())
{
}
public PersistableXml(XmlNamespaceManager nsmgr)
{
NamespaceManager = nsmgr;
}
[XmlIgnore]
public string String
{
get
{
return _stringData;
}
set
{
StringToXml(value);
}
}
[XmlIgnore]
public IXPathNavigable Doc { get; private set; }
[XmlIgnore]
public XmlNamespaceManager NamespaceManager { get; set; }
private void StringToXml(string xml)
{
if (string.IsNullOrEmpty(xml))
{
_stringData = string.Empty;
Doc = null;
return;
}
Doc = xml.ToNavigableXml();
if (Doc == null || ((XmlDocument)Doc).DocumentElement == null)
_stringData = string.Empty;
else
_stringData = ((XmlDocument)Doc).DocumentElement.OuterXml;
}
}
Now that we have the XML Type we need a Class file that makes use of this which we will eventually map to in the mapping file. In the following example you can see a really simple example. Keep in mind that the use of the private field is required and Auto Properties will not work. This is a limitation in NHibernate.
using System.Xml.XPath;
public class TradeDocument : ITradeDocument
{
private PersistableXml _document;
public virtual IXPathNavigable Document
{
get { return _document.Doc; }
set { _document = new PersistableXml(value); }
}
}
As you can see, given the pattern we employed we do not have to expose the XML abstractions beyond the mapped class files. In this case, the rest of the application sees only an IXPathNavigable object.
Ok, we are almost there. Now we just need to map the two together and we are all set.
<property name="Document"
column="DocumentXml"
access="field.camelcase-underscore"
type="Makler.Repositories.NHibernateDataTypes.XmlDataType, Makler.Domain"
insert="true"
update="true"
not-null="false"/>
Tags: xml, design patterns