5

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

Improve this question

2 Answers 2

Reset to default
2

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 |
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

I had considered this to be honest, but there is so much about the search results that need to be customisable (orderby column, number of results, offset of the result set) that it seems much easier to build up the query in linq, if at all possible.
However, +1 for that SQL Fiddle link - I've not seen that before, but it looks handy!
Dynamic SQL is the answer to dynamic query building. If you want to specify number of results you can dynamically embed a ` + ' TOP ' + @results + ' ' + ` into the building of your SQL statement to be executed. Order by in the same way. I usually add a ` WHERE 1 = 1 ` to handle default where criteria and allow you to add none or as many criteria as you want to cull down the data. I second the going of this route.
This is one giant security hole.
@flup would you care to explain a bit about what you mean? If you are referring to sql-injection it would be interesting to see if you could provide a XPath that does that.
|
-1

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.

Improve this answer

1 Comment

Does this execute the query, and then apply the order, skip and take locally? I'm going to test this, and if it works, I'm going to accept this as the answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.