0

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

4 Answers 4

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

1
  • So, which case should the where clause be? (You use both upper and lower case in your two examples.) -- Also, where do you get your query object? -- the Microsoft.SharePoint.Client.CamlQuery object doesn't even have a .Query property... Jun 17, 2016 at 20:39
0

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

0

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.

3
  • 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, 2011 at 21:57
  • 1
    Oh, sorry. In that case <IsNotNull> will do the trick. Feb 18, 2011 at 8:34
  • You need to escape your quotes; also you shouldn't just append text into the middle of an XML query like this, as it can lead to invalid markup, or open up security holes (i.e. CAML query injection), you should use an API (such as System.Xml.Linq) to build your XML that will properly escape your values for you. Jun 17, 2016 at 20:53
0

Just copy and Modify as per your requirement.

 string query = "<Where><Eq><FieldRef Name=\"Title\" /><Value Type=\"Text\">2019</Value></Eq></Where><QueryOptions/>";

Your Answer

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

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