CAML Client Side Object Model (CSOM) PnP PowerShell PowerShell SharePoint SharePoint Online 

SharePoint Online: How to Use CAML Query in PowerShell?

Salaudeen Rajack 0 Comments , ,

Requirement: Use CAML Query with PowerShell in SharePoint Online.

CAML is an XML-based query language, It stands for Collaborative Application Markup Language and used to query against items from SharePoint lists and libraries.

CAML Query Elements and Operators:

Here is 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 nameds 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 CAML query in PnP PowerShell

#Config Variables
$SiteURL = "https://crescenttech.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 on descending order:

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

Greater than, Greater than or Equal To, Less Than, Less Than or Equal To Operators on Currency Field:
Lets 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>

Combining Multiple AND and OR Operators in PowerShell CAML Query:
Lets combine multiple AND and OR operators to get all projects with “Total Investment” value between 5000 to 10000 and 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>"

When you have multiple “And” operator in the CAML query, the validation starts from the top to bottom.

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 value (Same as SQL Server “in” operator). Here is an example for “IN” operator in CAML Query: This gets all list Items where the “Project Name” column value is in any of the listed options under 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:
Lets 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:
Lets 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 to be retrieved from the list. E.g. You want to get only “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 scope parameter), all files and folders in the root folder of the list or library is 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:

<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 Folder 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 2013 which works with both SharePoint On-premises and SharePoint Online.

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 in the CAML query by its 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 <Eq> tag without the closing “</Eq>”

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

Leave a Reply