A while back I wrote about the limitations of LINQ to SQL when querying data in XML columns. It was frustrating but I hadn’t figured out an elegant solution at the time.
Then at then end of 2010 I attended a DDD8a session which re-opened my eyes to SQL CLR functions and how much better these were than SQL Scalar functions.
And today I linked the two problems in my head – the solution is right there! Write some SQLCLR functions which allow you to query an XML column at the server, rather than having to retrieve the full XML value and search it on the client.
Here is the VB.NET code I wrote as a first test to get the first element (or child element) of a given name and return the value.
'Get value of an Xml column element (including child elements) <Microsoft.SqlServer.Server.SqlFunction()> _ Public Shared Function XmlGetElementValue(ByVal column As SqlXml, ByVal elementName As SqlString) As SqlString 'Check for nulls If column.IsNull Then Return SqlString.Null If elementName.IsNull Then Return SqlString.Null 'Create an Xelement object from the XML Dim e As XElement = XElement.Parse(column.Value) 'Try to find an element of given name Dim found = e.Descendants(elementName.Value).FirstOrDefault If found IsNot Nothing Then 'return value Return New SqlString(found.Value) Else Return SqlString.Null End If End Function