SharePoint Online: How to Use CAML Query in PowerShell?

Requirement: Use CAML Query with PowerShell in SharePoint Online.

CAML is an XML-based query language, which stands for Collaborative Application Markup Language, and is used to query against items from SharePoint lists and libraries. This post will teach you the basics of using the CAML query in PowerShell. We’ll show you how to use a CAML query to filter and sort SharePoint data, and we’ll also provide a few examples to help you get started.

CAML Query Elements and Operators

CAML queries are XML-based structured text statements that tell SharePoint precisely what data to retrieve from its databases. Some key points:

  • CAML enables filtering, sorting, grouping, and other data-shaping functions.
  • Queries target specific SharePoint lists and libraries to pull back data.
  • The XML schema contains a root <View> tag with nested tags to define options.
  • CAML is used in many SharePoint APIs and web services by applications.

Here are the commonly used elements and operators in CAML queries:

Operator Meaning
Eq Equal To
Neq Not Equal To
Gt Greater Than
Geq Greater Than or Equal To
Lt Less Than
Leq Less Than or Equal To
Contains Contains
IsNull Null
IsNotNull Not Null
BeginsWith Begins With Text
In Value in any of the given values
NotIn Value not in any of the given values
Includes Same as Eq – In Multi-select Lookup columns, Checks if the given values are present.
NotIncludes Just does the opposite to “Includes” – Same as Not Eq in Multiple values
OrderBy Specifies the sort order
Where Applies filter to data
ViewFields Fields to retrieve
RowLimit Number of Rows to retrieve

CAML query uses the internal name of the fields. So, all characters in the field names will be decoded (E.g., “Space” will be replaced with “_x0020_”, So the “Project Name” field name is replaced with “Project_x0020_Name”). You can read more at Display Name vs. Internal Name in SharePoint Column Names

SharePoint Online: CAML Query Example using PowerShell

Here is the CAML query to retrieve SharePoint list items. Let’s get all list items from the “Projects” list, where the “Project Name” field is equal to the “Innovation Metrics”.

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
 
#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
 
#Get Credentials to connect
$Cred= Get-Credential
  
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Get the List
$List = $Ctx.Web.lists.GetByTitle($ListName)

#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='ProjectName' /><Value Type='Text'>Innovation Metrics</Value>
            </Eq>
        </Where>
    </Query>
</View>"

#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

Write-host "Total Number of Items:"$ListItems.count

#Loop through each List Item
ForEach($Item in $ListItems)
{ 
    Write-host $Item.id
}

SharePoint Online: PnP PowerShell to Get List Items using CAML

Use the “Query” parameter to apply the CAML query in the PnP PowerShell cmdlet Get-PnPListItem

#Config Variables
$SiteURL = "https://Crescent.sharepoint.com"
$ListName = "Project Tasks"
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)
 
#CAML Query to Filter List Items
$Query = "<View><Query><Where><Eq><FieldRef Name='ProjectStatus' /><Value Type='Choice'>Completed</Value></Eq></Where></Query></View>"
 
#Get All List Items matching given query
$ListItems = Get-PnPListItem -List $ListName -Query $Query
 
#Loop through each Item
Write-host -f Green "Number of List Items Found:"$ListItems.Count
ForEach($ListItem in $ListItems)
{  
    Write-Host "Id :" $ListItem["ID"] 
    Write-Host "Title :" $ListItem["Title"]
}

CAML Query in SharePoint Online to Sort on Ascending or Descending Order

Here is the CAML Query PowerShell CSOM to sort results.

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
 
#Get Credentials to connect
$Cred= Get-Credential
  
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Get the List
$List = $Ctx.Web.lists.GetByTitle($ListName)

#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View>
    <Query> 
        <OrderBy><FieldRef Name='Title' /></OrderBy> 
    </Query> 
</View>"

#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

Write-host "Total Number of Items:"$ListItems.count

#Loop through each List Item
ForEach($Item in $ListItems)
{ 
    #Do Something
    Write-host $Item.id
    Write-host $Item["Title"]
}  

To sort in descending order:

<OrderBy><FieldRef Name='Title' Ascending='FALSE' /></OrderBy>

You can also use the GroupBy to group items based on the given group.

<View>
  <Query>
    <OrderBy>
      <FieldRef Name="Title" Ascending="TRUE"/>
    </OrderBy>
  </Query>
  <ViewFields>
    <FieldRef Name="Title"/>
    <FieldRef Name="Status"/>
    <FieldRef Name="AssignedTo"/>
  </ViewFields>
  <GroupBy Collapse="TRUE" GroupLimit="100">
    <FieldRef Name="Status"/>
  </GroupBy>
  <RowLimit>10</RowLimit>
</View>

In this example, let’s assume you have a SharePoint list named “Projects” with a choice column named “Status” that includes values like “In Progress”, “Completed”, and “Not Started”. You want to group the projects by their status.

Using Comparison Operators in CAML Queries

Greater than, Greater than or Equal To, Less Than, Less Than or Equal To Operators in the Currency Field:
Let’s get all items where the “Total Investment” value is greater than or equal to 5000

<View>
    <Query>
        <Where>
            <Geq>
                <FieldRef Name='TotalInvestment' /><Value Type='Currency'>5000</Value>
            </Geq>
        </Where>
    </Query>
</View>

Filtering People Picker Field Values using CAML

<View>
    <Query>
        <Where>
            <Eq>
               <FieldRef Name='ProjectManager' LookupId='TRUE'/>
               <Value Type='Integer'>
                   <UserID Type='Integer'/>
               </Value>
            </Eq>
        </Where>
    </Query>
</View>

To check if the current user is a member of a group in a person/groups column, use:

<View>
    <Query>
        <Where>
            <Membership Type='CurrentUserGroups'>
              <FieldRef Name='Project_x0020_Members'/>
            </Membership>
        </Where>
    </Query>
</View>

How to use Logical Joins in CAML?

CAML supports various query operations, including logical joins like <and>, <or>.

Say for example, To find items where Status is “Approved” and DueDate is greater than 2021-01-01, you can use the element to logically join these two conditions:

<View>
  <Query>
    <Where>
      <And>
        <Eq>
          <FieldRef Name='Status'/>
          <Value Type='Text'>Approved</Value>
        </Eq>
        <Gt>
          <FieldRef Name='DueDate'/>
          <Value Type='DateTime'>2021-01-01T00:00:00Z</Value>
        </Gt>
      </And>
    </Where>
  </Query>
</View>

Similarly, To find items where Status is either “Approved” or “Pending”, use the “Or” element.

Combining Multiple AND and OR Operators in PowerShell CAML Query:
Let us combine multiple AND and OR operators to get all projects with a “Total Investment” value between 5000 and 10000, and the project department is either “Sales” or “Marketing”.

<View>
    <Query>
        <Where>
            <And>
                <And>
                    <Geq>
                        <FieldRef Name='TotalInvestment' /><Value Type='Currency'>5000</Value>
                    </Geq>
                    <Leq>
                        <FieldRef Name='TotalInvestment' /><Value Type='Currency'>10000</Value>
                    </Leq>
                </And>
                <Or>
                    <Eq>
                        <FieldRef Name='ProjectDepartment' /><Value Type='Choice'>Sales</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name='ProjectDepartment' /><Value Type='Choice'>Marketing</Value>
                    </Eq>
                </Or>
            </And>
        </Where> 
    </Query>
</View>"

The validation starts from the top to the bottom when you have multiple “And” operators in the CAML query.

Comparing Values using CAML Query in SharePoint

SharePoint Online CAML Query in PowerShell: The “IN” Operator
The “In” operator is a replacement for multiple “OR” conditions. It validates whether items are in any of the given values (Same as SQL Server “in” operator). Here is an example of the “IN” operator in CAML Query: This gets all list Items where the “Project Name” column value is in any of the listed options under the In operator:

<View>
    <Query>
        <Where>
            <In>
                <FieldRef Name='ProjectName' />
                <Values>
                    <Value Type='Text'>Address Book</Value>
                    <Value Type='Text'>Announcements</Value>
                    <Value Type='Text'>Birthday</Value>
                    <Value Type='Text'>Invitation</Value>
                </Values>
            </In>
        </Where>
    </Query>
</View>

Get List Items where a Text Filed Contains particular Value:
Let’s get all list items where the Project name contains “Inv” text in it, such as “Invoice,” “Yearly Invoice,” “Monthly Inv,” etc.

<View>
    <Query>
        <Where>
            <Contains>
                <FieldRef Name='ProjectName' /><Value Type='Text'>Inv</Value>
            </Contains>
        </Where>
    </Query>
</View>

CAML Query Example to Get All List Items where a Particular Field is Not Null:
Let’s get all projects where the Project description has some value (not null)

<View>
    <Query>
        <Where>
            <IsNotNull><FieldRef Name='ProjectDescription' /></IsNotNull>
        </Where>
    </Query>
</View>

CAML Query ViewFields in PowerShell

The “ViewFields” node in the CAML defines what fields are to be retrieved from the list. E.g., You want to get only the “ProjectName,” “Department”, and “City” fields from the list.

<View>
    <ViewFields>
        <FieldRef Name='ProjectName'/>
        <FieldRef Name='Department' />
        <FieldRef Name='City' />
    </ViewFields> 
</View>"

The “View Scope” in SharePoint Online CAML Query

By default, (If you don’t specify anything for the scope parameter), all files and folders in the root folder of the list or library are considered as scope.

Get All Files and Folders in All Levels deep

<View Scope='RecursiveAll'>  
</View>

Get All Files from the Root Folder:

<View>  
      <Query> 
            <Where>
                 <Eq>
                     <FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value>
                </Eq>
           </Where> 
       </Query> 
</View>

Get All Folders from the Root Folder of the List:

<FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value>

Get All Files from All Folders of the List:

The most basic CAML query simply retrieves all files from a target SharePoint library.

<View Scope='RecursiveAll'>  
            <Query> 
               <Where>
                     <Eq>
                           <FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value>
                     </Eq>
               </Where> 
            </Query> 
</View>

SharePoint Online CAML Query to Get All Folders from All Folders of the List:

<View Scope='RecursiveAll'>  
            <Query> 
               <Where>
                     <Eq>
                           <FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value>
                     </Eq>
               </Where> 
            </Query> 
</View>

Get All Items from a Specific Sub-Folder of the List:

Refer: SharePoint Online: Get All List Items from a Sub-Folder using PowerShell

SharePoint CAML Query Builder Tool

There are tools available to generate CAML Query. My favorite CAML query builders for SharePoint Online are U2U CAML Query Builder and CAML Designer. These tools works with all versions of SharePoint, including SharePoint On-premises, SharePoint foundation and SharePoint Online (Although there is a slight variation on SharePoint Server version).

Common Errors and Solutions

Error: “One or more field types are not installed properly. Go to the list settings page to delete these fields.”

One or more field types are not installed properly. Go to the list settings page to delete these fields

Solution: Fields are referred to in the CAML query by their Internal name! Make sure the referred field names are valid.

Error: Cannot complete this action

cannot complete this action. please try agian

Solution: The CAML query is not well-formed! E.g., You may have a <Eq> tag without the closing “</Eq>”.

Conclusion

In closing, mastering CAML querying is an essential skill for unlocking SharePoint list data efficiently across solutions. With the power of CAML combined with SharePoint REST API and object models, you gain flexibility in filtering, sorting, grouping, joins and more – avoiding inefficient brute force options. Use the below articles on learn more on how to use CAML queries in SharePoint Online.

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

Your email address will not be published. Required fields are marked *