Recently I had to retrieve some data from SQL that was tucked away inside XML. We have a “Settings” class inside our application that is a bunch of public properties, which we then serialize to XML to store inside the DB. This lets us easily add/remove fields to store without having to modify the database.
This can be a problem if you need to access those one of those field values from within SQL, but SQL 2005 provides some methods to do this (note that your data column must be the xml data type, I haven't found a way to get this to work by casting an ntext field to xml):
Providing your class looks like this:
[XmlSerializable]
public class GlobalSettings
{
[XmlElement]
public int SomeValue;
}
Then when serialized, your XML will look similar to this:
<GlobalSettings>
<SomeValue>5</SomeValue>
</GlobalSettings>
In SQL Management studio, you can write this (assuming GlobalSettings is the table and GlobalSettingsXML is the column where this data is stored):
SELECT GlobalSettingsXML.query('(/GlobalSettings/SomeValue)') FROM GlobalSettings
This will return:
<SomeValue>5</SomeValue>
Which may not be as useful as we want. :) To get the actual value (5) from this, we need to use the value() function:
SELECT GlobalSettingsXML.value('(/GlobalSettings/SomeValue)[1]', 'int') FROM GlobalSettings
This will give us '5', cast to an int. Per the MSDN helps on value(), the [1] is required after your xpath expression because the expression is supposed to return a singleton. I'm not quite sure what that means or how the [1] denotes it, but it works (if you know, by all means tell me!).
Hopefully this will be of use to you.