SQL Server 2005 : Using XML Columns

Had cause to play around with XML Columns in SQL Server 2005 over the weekend on a personal project im working on (shameless plug : www.milanxbl.com).

To cut a long story short, I ended up having a (well formed) XML document saved into an XML Column in the database that I needed to break down into its component parts in order to process the info into tables which already existed. This XML contained 2 parts in a master/detail style - the detail section contained a collection of items and the master section was just a header with several values.

I broke apart the master chunk of the xml using the following TSQL

SELECT UniqueIDColumn
,xmlcolumn.value('(/Root/XMLValue1)[1]', 'varchar(max)') AS XMLValue1
,xmlcolumn.value('(/Root/XMLValue2)[1]', 'varchar(max)') AS XMLValue2
,xmlcolumn.value('(/Root/XMLValue3)[1]', 'varchar(max)') AS XMLValue3
,xmlcolumn.value('(/Root/XMLValue4)[1]', 'varchar(max)') AS XMLValue4

FROM dbo.TableName WITH (Nolock)

In the example above, we query the content of the xml column (xmlcolumn) for the each value and bring back a normal looking table for further manipulation.

Obviously, traversing the nodes within the detail elements of the xml was slightly more complex and was done using cross apply to return to the node which contained the collection, like this:

SELECT UniqueIDColumn
,cast(R.i.query('./XMLValue1[1]/text()') as varchar(max)) as XMLValue1
,cast(R.i.query('./XMLValue2[1]/text()') as varchar(max)) as XMLValue2
,cast(R.i.query('./XMLValue3[1]/text()') as varchar(max)) as XMLValue3
,cast(R.i.query('./XMLValue4[1]/text()') as varchar(max)) as XMLValue4
,cast(R.i.query('./XMLValue5[1]/text()') as varchar(max)) as XMLValue5

from dbo.TableName WITH (NOLOCK)
cross apply xmlcolumn.nodes('/Root/Node2/Node3/Node4') R(i)

In this example we don't see any reference to the xmlcolumn until we see the 'cross apply' - there we see that the xml column has a different syntax, we are joining the column to itself using the table valued function (R) and navigating down the (i) axis for the nodes. This all begins to look a little 'CUBE-esce' when you think about it.

Microsofts official word on the use of the APPLY keyword, says
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

For more info on the APPLY operator, refer to MSDN SQL Server Developer center

I suspect this will be very useful, its just a matter of time before an application outside of my pet project warrants its usage.

Not yet had a chance to look at performance of this process - its not really an issue for my project - this is all happending 'under the hood' in a pre-processed manner, rather than being done at runtime in any live queries.

Comments are welcome!