通过实体框架使用用户提供的XPath查询SQL Server xml列 [英] Querying SQL Server xml column with user provided XPath via Entity Framework

问题描述

我很难确定如何在SQL Server中使用xml数据列，尤其是与Entity Framework一起使用。

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

其中一项要求但是，我们的应用程序支持搜索元数据。用户可以提供XPath查询字符串以及用于与XPath值进行比较的值，以搜索包含与他们的查询匹配的元数据的元素。

<我确定SQL Server xml函数对此是理想的（例如[xmlcol] .exist（'/ path1 / path2 [0] [text（）=''valuetest''']]），但不支持它们或者，我尝试创建一个将用户提供的XPath传递给xml函数的UDF，但是后来发现xml函数仅允许字符串文字，因此我无法提供变量...

这时，我的选项已用完。

我创建了一小段代码，对IQueryable.ToString（）的结果执行正则表达式替换，以注入我的XPath过滤器，然后将该字符串手动发送到数据库，但是这样做也存在问题，例如结果似乎没有延迟加载例如，导航属性。

我一直在寻找，偶然发现了SQLCLR类型的概念，并开始创建执行XPath比较的SQLCLR函数。我以为这是一个胜利者，直到一位同事指出Azure中的SQL Server不支持SQLCLR-哦！

我还有其他选择吗？ ？我似乎快要接近空了……

解决方案

您可以在存储查询的存储过程中执行此操作

SQL提琴

MS SQL Server 2008架构设置：

 创建表YourTable 
（
 ID身份标识主键，
名称varchar（10）不为null，
 XMLCol xml 
）; 
 
去
 
插入YourTable值
（'Row 1'，'< x> 1< / x>'），
（'第2行，'x 2 / x'），
（'第3行'，'x 3 / x'）; 
 
进行
 
创建过程GetIt 
 @XPath nvarchar（100）
 as 
开始
声明@SQL nvarchar（max ）; 
 
 set @SQL = N'
选择ID，从YourTable 
中命名
其中XMLCol.exist（'+ quotename（@XPath，''''）+ N'）= 1'; 
 
 exec（@SQL）; 
结束
  

查询1 ：

  exec GetIt N'* [text（）= 2]'
  

结果 ：

  | ID | NAME | 
 -------------- 
 | 2 |第2行| 
  

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

解决方案

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 |

这篇关于通过实体框架使用用户提供的XPath查询SQL Server xml列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！