How to query SQL Server table with XML column (as string) using Linq to SQL?

Question

How can I use LINQ to SQL to query a table with an XML datatype column as an IQueryable?

SQL Server table:

Id [int]
SomeData [xml]

An example of SomeData could be:

<container><Person>Joe Smith</person></container>

In T-SQL, I can query the table like this to find employees with a specific nam,e :

SELECT 
    *
FROM
    [MyTable]
WHERE 
    SomeData.value('(//*[local-name()="Person"])[1]', 'varchar(max)') = 'Joe Smith'

Or to treat the xml as a string, to search for a string regardless of xml schema:

SELECT 
    *
FROM
    [MyTable]
WHERE 
    CONVERT(varchar(max), SomeData) like '%Joe Smith%'

Now I turn to LINQ to SQL, and want to perform the second query:

var myTable = db.MyTable.Where(x => x.SomeData.Value.Contains("Joe Smith"));

I get the error:

"The member 'System.Xml.Linq.XElement.Value' has no supported translation to SQL.

I understand why I get the error, but is there a way to work around it?

Being able to query the column as a string (with no concept of xml-structure) is completely fine in this case.

Phil · Accepted Answer · 2013-02-19 10:27:15Z

1

Create a view that exposes the Xml column as varchar(...) and query that.

answered Feb 19, 2013 at 10:27

Phil

43.2k9 gold badges102 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ethan Drotning · Accepted Answer · 2016-06-14 22:01:21Z

1

Instead of Contains you can use System.Data.Linq.SqlClient.SqlMethods.Like().

var myTable = db.MyTable.Where(x => SqlMethods.Like(x.SomeData, "%Joe Smith%"));

answered Jun 14, 2016 at 22:01

Ethan Drotning

111 bronze badge

Leventis1984 · Accepted Answer · 2013-02-19 10:31:53Z

0

Import xml file into a temporary table with a xml column and THE try to parse the file and put it into a relational table.

answered Feb 19, 2013 at 10:31

Leventis1984

827 bronze badges