Querying Sql Server Xml Column With User Provided Xpath Via Entity Framework

I'm having a really tough time figuring how to use an xml data column in SQL Server, specifically for use with Entity Framework. Basically, one of our tables stores 'custom metadat

Solution 1:

You could do this in a stored procedure where you build your query dynamically.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

createtable YourTable
(
  ID intidentityprimary key,
  Name varchar(10) notnull,
  XMLCol xml
);

go

insertinto YourTable values
('Row 1', '<x>1</x>'),
('Row 2', '<x>2</x>'),
('Row 3', '<x>3</x>');

go

createprocedure GetIt
  @XPath nvarchar(100)
asbegindeclare@SQL nvarchar(max);

  set@SQL= N'
  select ID, Name
  from YourTable
  where XMLCol.exist('+quotename(@XPath, '''')+N') = 1';

  exec (@SQL);
end

Query 1:

exec GetIt N'*[text() = "2"]'

Results:

| ID |  NAME |
--------------
|  2 | Row 2 |

Solution 2:

To remain "customisable", the SqlQuery method on DbSet can be used:

var query = @"SET ARITHABORT ON; 
              select * from [YourTable] where 
              [xmlcol].exist('/path1/path2[0][text()=''{0}''']";
var numOfResults = 5;
var offsetPage = 1;

var results = Context.YourTable.SqlQuery(String.Format(query,"valuetest"))
                              .OrderBy(x => x.col)
                              .Skip(offsetPage * numOfResults)
                              .Take(numOfResults).ToList();

Note, due to its dynamic nature, this method would also most likely expose some degree of sql injection security holes.