Querying XML fields in LINQ to SQL

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
Advertisements

Serializing Generics

Lets say I want a generic list of something in XML:

e.g.

Public Property AccountNumbers As List(of String)

when we serialize this, the serializer helpfully defaults to the Type of the generic (in this case, string), so we would get the XML:

<AccountNumbers>
  <string>1234</string>
  <string>5678</string>
</AccountNumbers>

Which works but isn’t very reader friendly. If we want to change the <string> to something else, we decorate the property with the XmlArrayItem attribute:

<System.Xml.Serialization.XmlArrayItem("Account")> _
Public Property AccountNumbers As List(of String)

And so we get the nicely formatted result:

<AccountNumbers>
  <Account>1234</Account>
  <Account>5678</Account>
</AccountNumbers>