CODEWITHSUNDEEP
sql-serverentity-frameworkxpath
dark_perfect
dark_perfect

Reputation: 1478

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 metadata" provided by users in the form of XML, so it seemed sensible to store this in an Xml column in the table.

One of the requirements of our application is to support searching of the metadata, however. The users are able to provided an XPath query string, as well as a value to compare the value of the XPath with, to search for elements that contain metadata that matches their query.

I identified the SQL Server xml functions as ideal for this (eg, [xmlcol].exist('/path1/path2[0][text()=''valuetest'''] ), but they're not supported by Entity Framework, irritatingly (or specifically, xml columns aren't supported). As an alternative, I tried creating a UDF that passes the user-provided XPath to the xml functions, but then discovered that the xml functions only allow string literals, so I can't provide variables...

At this point, I was running out of options.

I created a small bit of code that performs a regular expression replace on the result of a IQueryable.ToString(), to inject my XPath filter in, and then send this string to the database manually, but there are problems with this too, such as the result doesn't seem to lazily load the navigational properties, for example.

I kept looking, and stumbled upon the idea of SQLCLR types, and started creating a SQLCLR function that performs the XPath comparison. I thought I was onto a winner at this point, until a colleague pointed out that SQL Server in Azure doesn't support SQLCLR - doh!

What other options do I have? I seem to be running very close to empty...

Upvotes: 5

Views: 3846

Answers (2)

Bermo
Bermo

Reputation: 4931

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.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

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

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table YourTable
(
  ID int identity primary key,
  Name varchar(10) not null,
  XMLCol xml
);

go

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

go

create procedure GetIt
  @XPath nvarchar(100)
as
begin
  declare @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 |

Upvotes: 2

Related Questions