LINQ to SQL FAQ – XML columns in SQL

XML datatype columns were added to Microsoft SQL server in version 2005. If your database contains an XML column, LINQ to SQL v1 will by default generate this as an XElement property in your table class.

However, LINQ to SQL does not appear to support ANY translations of XML queries on XElement to SQL – this mans that the following statement :

var filtered = from t in Table
                    where t.XmlCol.Descendants("SomeElement").Value == somevar
                    select t

will fail at runtime as the LINQ-to-SQL parser is unable to translate on our XML data column XmlCol query into an Xpath.

Workarounds

If the dataset isn’t large, it is possible to load all the values, and query them, e.g. :

var all = (from t in Table select t).ToList();
var filtered = from t in all
                    where t.XmlCol.Descendants("SomeElement").Value == somevar
                    select t

This includes the capability to query the XML using an Xpath query so that it is not necessary to load all the XML from an XML column to search or query it. This is obviously inefficient and not desirable.

The quick and dirty fix for this is to convert the XmlCol to a string and do a string search. This is possible but not really very safe since the search string is not using XML structure and can produce incorrect results.

Use a SQL Function ? Forget it

I thought that a way around this might be to create a SQL function that takes an XPath path and a value and can compare the XML Xpath query and the variable.

Alas this is not possible: SQL 2005 supports XMLcol.value(path, type) but does NOT support use of variables for ‘path’ position – so this cannot be written as a function – this is of course bizarre – it means all XPath queries have to be done either as fixed functions or as inline SQL. So, not only can we not query the XML column in LINQ to SQL we cannot even create a SQL scalar function to do this. It may be fixed in SQL 2008 as SQL support for XML data seems to have improved.

Updating XML Columns – Beware

A LINQ to SQL xml datatype column is represented as an XElement. If you modify this be careful as LINQ to SQL will not spot internal changes to XElement and save them to the database.

For example:

Dim myElement as XElement = Table.XMLdata
myElement.Value = "test"
db.SubmitChanges()

will not alter the instance XElement and so will not change the Table.XMLdata entry. The workaround for this is to write it back as a new element:

Dim myElement as XElement = Table.XMLdata
myElement.Value = "test"
Table.XMLdata = new XElement(myElement)
db.SubmitChanges()

Microsoft Efforts

This Oakleak article suggests that MS is aware of the inability to query XML fields. and a powerful extension called LINQ to Stored XML is under development. Let’s hope it arrives soon!

Advertisements

4 thoughts on “LINQ to SQL FAQ – XML columns in SQL

  1. I’ve commented on another post of yours, but FYI, here is a SP we have to drill into XML data…

    CREATE FUNCTION [dbo].[udfGetXpathValueString] 
    (
          @xml xml,
          @field varchar( 50 ),
          @container varchar( 50 ) = NULL
    )
    RETURNS nvarchar( 4000 )
    AS
    BEGIN
          DECLARE @result nvarchar( 4000 )
    
          SELECT @result =
                CASE
                      WHEN @container IS NOT NULL THEN
                            @xml.value( '(/*[local-name()=sql:variable("@container")]/@*[local-name()=sql:variable("@field")])[1]', 'nvarchar( 4000 )' )
                      ELSE
                            @xml.value( '(/*/@*[local-name()=sql:variable("@field")])[1]', 'nvarchar( 4000 )' )
                END
    
          RETURN @result
    
    END
    
    • That’s interesting thanks – so it is possible to do an XPath query in TSQL!

      However, I’d suggest you read my other posts about the performance issues of SQL Scalar functions vs CLR functions and the performance comparisons. TSQL functions are horribly slow by comparison.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s