使用用户提供的XPath通过实体框架查询SQL Server xml列

I'm having a really tough time figuring how to use an xml data column in SQL Server, specifically for use with Entity Framework.

我很难确定如何在SQL Server中使用xml数据列,特别是与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.

基本上,我们的一个表存储用户以XML形式提供的“自定义元数据”,因此将其存储在表中的Xml列中似乎是明智的。

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.

但是,我们的应用程序的一个要求是支持搜索元数据。用户能够提供XPath查询字符串以及用于比较XPath值的值,以搜索包含与其查询匹配的元数据的元素。

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...

我确定SQL Server xml函数是理想的(例如,[xmlcol] .exist('/ path1 / path2 [0] [text()=''valuetest''']),但它们不受实体支持框架,恼人地(或者特别是,不支持xml列)。作为替代方案,我尝试创建一个UDF,将用户提供的XPath传递给xml函数,但后来发现xml函数只允许字符串文字,所以我无法提供变量......

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.

我创建了一小段代码,在IQueryable.ToString()的结果上执行正则表达式替换,将我的XPath过滤器注入,然后手动将此字符串发送到数据库,但是这也存在问题,例如例如,结果似乎没有懒惰地加载导航属性。

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!

我一直在寻找,偶然发现了SQLCLR类型的想法,并开始创建一个执行XPath比较的SQLCLR函数。我认为此时我已成为赢家,直到一位同事指出Azure中的SQL Server不支持SQLCLR - doh!

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

我还有其他选择吗?我好像跑得很近......

2 个解决方案

#1

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

您可以在存储过程中执行此操作,您可以在其中动态构建查询。

SQL Fiddle

MS SQL Server 2008 Schema Setup:

MS SQL Server 2008架构设置:

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 |

#2

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

要保持“可自定义”,可以使用DbSet上的SqlQuery方法:

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.

请注意,由于其动态特性,此方法也很可能会暴露某种程度的sql注入安全漏洞。

#1