Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have the following CAML query:

<Where>
<In>
  <FieldRef Name='ObjectID' />
  <Values>
    <Value Type='Integer'>71</Value>
    <Value Type='Integer'>72</Value>
  </Values>
</In>
</Where>

But what I want in the result set if effectively the negation of that, or items that do not meet the condition.

Is there any way I can achieve this with a relatively simple query.

I'm new to CAML and I can't find anything like a Not operator.

I looked at this question but it's on 2007, apparently before the <In> element was introduced. Apologies for the title, it wouldn't let me post it with a sensible one because of the question linked to.

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

In is just a short form that saves you from writing a big long <Or><Eq>... statement. In your case, you can do <And><Neq>...

<Where>
   <And>
     <Neq>
       <FieldRef Name='ObjectID' />
       <Value Type='Integer'>71</Value>
     </Neq>
     <Neq>
       <FieldRef Name='ObjectID' />
       <Value Type='Integer'>72</Value>
     </Neq>
   </And>
</Where>
share|improve this answer
    
Is <In> just syntactic sugar then? I assumed it would have better performance than massive nested statements. –  TZHX Nov 2 '12 at 14:06
    
Good question - it looks like IN vs OR in SQL Server doesn't make a difference, so beyond maybe a tiny hit for parsing through more XML it's syntactic sugar - stackoverflow.com/questions/2110715/sql-server-select-in-vs-or –  lgaud Nov 2 '12 at 17:29
    
From a SQL performance point of view <In> is purely suntactic sugar, but SharePoint can handle a lot more values in <In> before it breaks. –  Per Jakobsen Nov 4 '12 at 17:24
add comment

I'm not sure any Not operator exists, so I guess you have to fetch all IDs and do a Where.

However, you can be tricky about it, since IDs generated in SharePoint start from 1 and steadily increment upwards. You can't know the last ID just by counting items, one could delete a bunch in the middle, so you need to:

  • Find the ID of last inserted item.
  • Create a <Values> element containing all ids up to the largest ID, except those you don't want.
  • Run a CAML with In.

I guess this would probably just as slow as a Where, or slower. But more fun, and fun is good.

share|improve this answer
    
Hi! Thanks for your response. I think it's probably going to have to be something like this, but the only problem is ObjectID isn't actually a sharepoint ID field (probably bad name for it). But I should be able to do something along these lines. –  TZHX Nov 2 '12 at 10:12
    
+1 - but I'll leave the question open for a little longer to see if anyone has a more direct solution. –  TZHX Nov 2 '12 at 10:13
    
The column not being ID changes the matter. You could also build a huge CAML by nesting And and Neq, which again can be made easier through U2U CAML Builder or CodeCaml. But still manual, and quite messy. –  eirikb Nov 2 '12 at 10:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.