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, and it stands for Collaborative Application Markup Language and is used to query against items from SharePoint lists and libraries. This post will walk you through 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:
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 CAML query in 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>
Greater than, Greater than or Equal To, Less Than, Less Than or Equal To Operators in 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>
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.
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 for 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 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 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 favourite CAML query builders for SharePoint Online are U2U CAML Query Builder and CAML Designer 2013, which work with 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.”
Solution: Fields are referred in the CAML query by their Internal name! Make sure the referred field names are valid.
Error: Cannot complete this action
Solution: The CAML query is not well-formed! E.g., You may have a <Eq> tag without the closing “</Eq>”.
