Using Caml Query Sharepoint List

Traditionally, Microsoft SharePoint list data is filtered using CAML query. But when querying the same SharePoint list data from Microsoft flow, CAML query will not work because Microsoft flow was built on top of Logic apps. So, to query the SharePoint list data in Microsoft Flow, OData filter expressions need to be used instead of CAML query. Get SharePoint Items By CAML Query is the action from Plumsail SharePoint connector. You can use it to get various information about items or documents by using CAML query. Apr 11, 2015 CAML QUERY to retrieving the items from a list using CSOM Next Recommended Reading Update A List Item In SharePoint Using CSOM (Announcement List).

Filtering List Item using CAML query in sharepoint. By rautchetan27 on May 25, 2020. 1504 Views 0 Likes. Related Videos View all.
In this article, we will discuss how to query a SharePoint List using CAML in SharePoint 2010. SPQuery object has Query property that accepts a CAML fragment, which defines the query to be performed. A ViewFields property defines the fields to return.

This post demonstrates how to use the JOIN syntax in CAML.

SharePoint 2010 adds the ability to create relational lists. To use the JOIN operator in CAML, your lists must have a defined relation. Let’s start by creating the lists and their relationships.

Creating the Lists

I first add a standard Contacts list named “DSE” and populate it with some data.

Go to the list settings for your new contact list and hover over the column names. Many SharePoint developers forget this, the name you see in the web UI is the .Title property, which may be different than the internal name that SharePoint uses (the .InternalName property of the SPField object). For a quick way to see what the actual field name is, hover over the column name and look in the address bar in your browser, and we see that the “Last Name” column is actually the “Title” field.

Next, I create a custom list named “Projects” and add a column named “Manager”. The type is a lookup column. Note the name “Manager”, we’ll refer to this name in our code.

In the additional column settings section for the “Manager” column, I set the lookup to the ID column for the DSE list, and additionally show the First Name and Last Name.

When I add a new item to the Projects list, I get a drop-down that lets me select a value from the lookup column.

The result looks like this:

Now that we have the lists, a lookup field, and some data, let’s query it.

Querying Using JOINS and SPQuery

Nothing speaks louder than a code sample. At the top of my code, I add a few using directives.

The first thing to notice is the SPQuery.Joins property which lets me provide 1 or more joins for the list. The list must already have a lookup column and a relation defined to the list being joined to. Notice in the Joins property that we refer to the name that we provided when we created the lookup column, “Manager”.

The second thing to notice is the ProjectedFields property. This is where we tell SharePoint how to project the lookup columns into the result. In a contacts list, the “First Name” column has an internal name of “FirstName”. The “Last Name” column is actually the Title column (see above for how to determine the .InternalName of a field).

Finally, the ViewFields property lets us define which fields are included in the result. We use the same name that we used in the ProjectedFields property. This name could be anything, so long as the name in ProjectedFields and the name in ViewFields match.

The results are pretty unimpressive, but it proves that our CAML query works.

And there you have it, your first CAML JOIN query.

Querying Using LINQ

Just for kicks, I will throw in a LINQ sample, too. LINQ doesn’t support the JOIN operator, nor does it support a projection to a referenced entity. What you can do, though, is just reference the entity in the results.

Using Caml Query Sharepoint List Template

I opened the Visual Studio 2010 Tools command window and ran the following command:

This generates the strongly-typed entities for me behind the scenes with metadata that tells LINQ how to query the results. I include the result, PFE.cs, in my Visual Studio project and run the following code. Notice I add the ctx.Log setting to log output to the Console window. That lets me include a picture of a WHOLE BUNCH of CAML in the screen shot below.

Sharepoint Caml Query Tutorial

Using your own JOIN operation can be more efficient, but test the results to be sure. Often times LINQ will generate more efficient queries, but not always.

Sharepoint Caml Query Examples

I’ve been doing a lot of work with SPServices and SharePoint lists lately, and I find myself using the same CAML queries over and over. Unfortunately I don’t always remember how to format some of the more common queries, so I decided to make a quick reference.

There are already some good tools and resources out there for building CAML queries. U2U’s CAML Query Builder is usually what I fall back to when I need a complex query with multiple conditions. Matt Bramer’sroboCAML is shaping up very nicely as well. However, in many cases these are overkill and I just need a basic “get all items where field X is equal to value Y” query. Heck, sometimes I just want to hard-code the query myself because it’s good practice. A quick reference that shows what value type to use and in what format for the various field types is all I really need so I don’t have to hunt through several bookmarked articles to find the answer.

I intend to update this on occasion, so if you have any suggestions please leave them in the comments.

Column/Field Type	Value Type	Example
Single Line of Text	Text	This is one of the simplest queries. The example selects items with a title equal to “Hello World!”
Multiple Lines of Text	Text	If this is a Rich Text field, you can use `<![CDATA[]]>` around the value to prevent parsing errors when passing HTML into the query. Alternatively, you can encode the HTML by replacing `<` with `<`, `>` with `>`, and `'` with `"`. This query uses `<Contains>` to return any items that contain a hyperlink in the body field by looking for the closing `</a>` tag.
Person or Group (By Name)	Text	This will look for items created by any user with “Josh McCarty” in the Name field of the User Information list. If more than one person has the same display name in the user list, it will select items created by all users with that name.
Person or Group (By ID)	Integer	By adding `LookupId='TRUE'` to the `<FieldRef />` and using `<UserID />` as the value, the query will filter based on the current user. You can also pass the ID of a specific user in place of `<UserID />` (e.g. `<Value Type='Integer'>283</Value>`) if you don’t want to filter by the current user. IDs are always unique, so this method ensures that only one user is a valid value.
Lookup (By Text)	Lookup	This will look for items with “Arizona” in the State field. If more than one state has the same display name (not likely in this example, but for other lookups it could happen), it will return items from all states with that display name.
Lookup (By ID)	Lookup	By adding `LookupId='TRUE'` to the `<FieldRef />`, the query will filter based on the `ID` of the lookup rather than the text value. IDs are always unique, so this method ensures that only one state is a valid value.
Date (Day Only)	Date	This type of query seems to work whether the value type is set to `DateTime` or just `Date` as long as the value is formatted properly (yyyy-mm-dd). It also works if `<Today />` is used as the value (you can offset the current date; e.g. use `<Today OffsetDays='-7' />` for 7 days ago). See one of my previous posts regarding current date offsets for some more information about using `<Today />`.
ContentTypeId	ContentTypeId	This is useful for querying items with a specific content type or, if using `<BeginsWith>`, items with a specific content type or child content types. You can get the content type ID in the web UI by viewing that content type from the site collection or list content types (it should be in the URL).

Caml Query Sharepoint List Id

I’ll be adding more column types to this post as I have time to write them, and I’ll probably create a PDF once they are all finished. For now I wanted to get this published sooner rather than later. If you have any suggestions or sample queries, leave them in the comments!