Geek asks and answers

SQL Server xml column query with XPath provided by user through Entity Framework

I find it very difficult to understand how to use an XML data column in SQL Server, especially for use with the Entity Framework.

In principle, one of our tables stores "user metadata" provided by users in XML form, so it was convenient to store this in the Xml column in the table.

However, one of the requirements of our application is to support metadata search. Users can provide an XPath query string, as well as a value, to compare the XPath value with, to search for items containing metadata matching their query.

I defined the XML xml functions as ideal for this (for example, [xmlcol] .exist ('/ path1 / path2 [0] [text () =' 'valuetest' '']), but they are not supported by the Entity Framework, annoyingly ( or, in particular, xml columns are not supported.) As an alternative, I tried to create a UDF that passes the user-provided XPath for xml functions, but then found that xml functions only allow string literals, so I cannot provide variables ...

At this moment, I did not have enough options.

I created a small piece of code that performs regular expression, replacing the result of IQueryable.ToString () to enable my XPath filter, and then send this line to the database manually, but there are problems with this too, for example, the result doesn’t seem to lazily loads navigation properties, for example.

SQLCLR SQLCLR, XPath. , , , SQL Server Azure SQLCLR - doh!

? , ...

+5
2

, .

SQL Fiddle

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

1:

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

:

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

"", SqlQuery DbSet:

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();

, - , , SQL.

0

More articles:


All Articles