I have recently been looking at using the SQL 2005 XML column to store data that will eventually be used to create web pages in MOSS 2007. The first issue I faced was how to get the XML out of the column!

The first attempt involved reading it out as a string and using the System.Xml namespace in C# to manipulate the string, get nodes etc. I felt like there must be a better way and eventually came across the following article on XQuery:

http://www.15seconds.com/Issue/050803.htm

For those that don't want to trawl the whole article the syntax for selecting an XML data point is as follows:

Select

[XML Column Name].query(‘data([path to data point])')

From [Table Name]

So in my specific case this would be:

Select

Attributes.query('data(//product/volumesolid)') as volumesolid,

Attributes.query('data(//product/dft)') as dft,

From Product

Another useful article to understand when to use the XML column in SQL is ‘XML best practices with SQL 2005’ - http://msdn2.microsoft.com/en-us/library/ms345115.aspx