CODEWITHSUNDEEP
sql-serverlinqlinq-to-xmlxquery
silent
silent

Reputation: 4004

Is it possible to do XQuery through linq?

I have an XML field which has about 5MB of data every record, but sometimes, I only need to read a small part of the XML field. As you can imagine, if I read the whole XML field and then using Linq-to-XML to parse the XML file and gather the value, it would be too slow and expensive. So I want know, is it possible to get a value directly using Linq instead of read the whole XML field?

My DB is SQL Server 2008

Upvotes: 0

Views: 447

Answers (1)

Andreas Ågren
Andreas Ågren

Reputation: 3929

With the current information provided I think the best solution is to use an XML index in SQL Server.

There are four types of XML indexes:

  1. Primary
  2. Secondary for PATH
  3. Secondary for PROPERTY
  4. Secondary for VALUE

In your case it appears you know the path to the data you want, naturally a secondary PATH index seems to be the best fit.

Follow these steps to create this index:

Create primary index

create primary xml index XIX_XmlColumnName on XmlTable(XmlColumnName)
go

This will create the "base" index for your xml column, basically this means that the xml will be shredded to a hidden table and stored with values where every element is turned into one row.

Create secondary path index

create xml index XIX_XmlColumnName_Path on XmlTable(XmlColumnName)
using xml index XIX_XmlColumnName for path
go

This will create a secondary index using the path-column in the primary index (which we now know is a table).

Finally, run a (sql) query such as this in a procedure and call that from your application:

select XmlColumnName.query('/path/to/element')
from XmlTable

Granted, this is not a linq-query/solution, but imo it's always best to use a tool that fits, and not try to force it.

For more in-depth information about xml indexes, see this msdn-article.

Upvotes: 1

Related Questions