Searching a tables XML filed using Linq, can it be done?

Question

I have a database table with transactions. In one of the fields there is an XML message, the field type is “xml”. In this XML there is an employee filed and it is this field that I need to search. I want to retrieve all the rows that match the employee number supplied at runtime. Is it possible to do this with Linq ?

Shown here is some example XML from one of the rows in the transaction table. The field is called “Message”. I need to look at the “employee” value and return the row if it match what was supplied by the user.

<interface>
  <mac>1452345234</mac>
  <device>device1</device>
  <id>1234567</id>
  <terminal>
    <unit>1</unit>
    <trans>
      <event>A3</event>
      <employee>3333</employee>
      <time>2008-10-02T11:41:00.0000000+00:00</time>
    </trans>
  </terminal>
</interface>

Answer 1

Yes, it is easily possible with LINQ:

var matchList = from t in transactions
                where XDocument.Load (new StringReader (t.Message))
                               .Descendants ("employee")
                               .Count (node => node.Value == employeeNr) > 0
                select t;

Answer 2

Simple way:

List<YourRecord> GetRecords(string EmployeeId)
{
    return TheTable.where(r => r.Message.Contains("<employee>" + empId + "</employee>")).ToList();
}