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 would like to use CAML to write an efficient query to get all items in a list where a text field contains a value...

for example:

**ID                    Name**
123                     
234                     Mr Rocket
345                     Mr Science

In the query I want to the results to only include where items contain a value for the Name field.

Is there an easy way to do this? I don't want to iterate using object model (or LINQ), because this list is heavily populated.

Thanks

share|improve this question
add comment

3 Answers 3

CAML is good when you understand it:

I've included this to show you inclusion and exclusion of values that may be null:

 query.Query = "<Where><And><IsNotNull><FieldRef Name='YourTextField' /></IsNotNull><IsNull><FieldRef Name='EvenWorksOnHyperLinkFields' /></IsNull></And></Where>";

If you're new to CAML you may be wondering what that and is there for, when you write your CAML query you batch it off in pairs of 2. The operator exists on the outside of the query.

In SQL you would write:

Select * from table where field = 'value' and field2 = 'value' (for example)

In CAML - <where><And><criteria 1><criteria 2></And></Where> , worth mentioning its case sensitive too. And <> and....

share|improve this answer
add comment

I wanted to do some extra filtering with some CAML, specifically multiple nested AND's and OR's. I am only offering this in case you wanted to add more filtering for you query. Hope it helps.

Check it out here: CAML query with nested AND's and OR's for multiple fields

share|improve this answer
add comment

Actually it's very easy to retrieve records that contain your value in column "Name":

query.Query = "<Where><Contains><FieldRef Name="Name"/><Value Type="Text">" + "YourValue" + "/Value></Contains></Where>";

All you have to do is to remember Query Schema.
And here are some tip from me:

  1. To understand CAML better you can create View that filters your list and then open this View in SharePoint Designer and you will see the markup of CAML. By adding different filters you will get what you want and your knowledge of CAML will boost :).

  2. If you don't want to know what the hack is CAML -> use Linq to SharePoint. You write simple Linq query on the list and in the background it's translated to CAML.

share|improve this answer
    
Thanks Andriy, maybe I wasn't entirely clear in the question. When I mean a value, what I mean is ANY value, i.e: is not null or empty string. Sorry if this caused confusion. –  user879 Feb 17 '11 at 21:57
    
Oh, sorry. In that case <IsNotNull> will do the trick. –  Andriy Shvay Feb 18 '11 at 8:34
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.