XQuery in Linq To Entities for Sql Server XML Data Type

Question

I am trying to figure out how to do a Linq To Entities query on a SQL Server Database which contains an XML datatype:

I want to do a database level query that returns only a specified XML node that contains over 2000 Characters

Is this possible in Linq To Entities?

Update

well i want to do something like this in linq-to-entities but I don't think its possible

var q = from c in Changes
 where c.Content.XPathSelectElement("OpsNotes").Value.Length >= 2000;
 select c;

inside the sql server database the XML content is a node called "OpsNotes"

 <Content Type="AP" Version="1">
  <Name>CPU FAIL-OVER</Name>
  <Bands>
    <Band>BAND DATA</Band>
  </Bands>
  <StartTime IsCritical="false" IsTimeSet="true">2009-12-06T14:00:00.0000000Z</StartTime>
  <Duration>00:30:00</Duration>
  <RecurrenceRule>RRULE:[List]{340/2009}</RecurrenceRule>
  <OpsNotes>Rationale: Support standard weekly DVIS CPU fail-over</OpsNotes>
   </Content>

Id be keen to know if you found and answer for this, if so would you mind post here — Anthony Main
– Anthony Main, Commented Sep 28, 2010 at 23:25
sorry did not.. I think the answers below would help - We decided to go with a rest based service to post xml data instead of directly accessing the Entity Framework layer.. this solved our issue. — Space Programmer
– Space Programmer, Commented Jan 13, 2011 at 14:52

marc_s · Accepted Answer · 2010-07-22 21:51:33Z

1

I would be surprised if that were possible, directly.

You will most like need to do one of two workarounds:

retrieve the XML from the SQL Server field as a string, and put it into an XDocument (Linq-to-XML) or XmlDocument (DOM-based handling) and do your tricks on it

or:

handle the requests on SQL Server itself, using the SQL Server XQuery, and return results as string, ints etc. - whatever you need to do

I personally would prefer the option to handle the XQuery stuff on the server itself - in order to help you with your concrete problem, you'll have to provide some sample XML and an explanation of what you want to do....

Update: for your requirement, in SQL Server XQuery, you'd have to write something like:

var q = from c in Changes where c.Content.XPathSelectElement("OpsNotes").Value.Length >= 2000; select c;

SELECT (list of fields)
FROM dbo.Changes c
WHERE c.Content.value('(string-length(string((/Content/OpsNotes)[1])))', 'int') >= 2000

or something like that.

As an added bonus, in this case, you'd only transfer a smaller amount of rows back from the server to your client app since the server is already filtering out all the non-matching rows.

edited Jul 22, 2010 at 21:51

answered Jul 22, 2010 at 21:10

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Space Programmer Over a year ago

Yeah - i think I will try that for now.. thanks! - seems like XML dataype should be supported by Linq-To-Entities

Craig Stuntz · Accepted Answer · 2010-07-22 21:26:42Z

0

You'll have to write SQL for this.

In EF 4, you can use ExecuteStoreQuery to execute arbitrary SQL with entity-typed results.

answered Jul 22, 2010 at 21:26

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

1 Comment

Space Programmer Over a year ago

hmm not using EF4 although i've got it on LinqPad 4 i think -i will investigate

Collectives™ on Stack Overflow

XQuery in Linq To Entities for Sql Server XML Data Type

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related