SharePoint Online: CAML Query for Choice Field in PowerShell

Requirement: CAML Query for Choice Field in SharePoint Online using PowerShell.

Get List Items Where a Choice Field is Equal to Particular Value:

Let’s get all projects from the projects list, where the “Priority” choice field value is “Medium”.

#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 Object
$List = $Ctx.Web.lists.GetByTitle($ListName)

#Define the CAML Query - sharepoint caml query choice field
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='Priority' /><Value Type='Choice'>(3) High</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)
{
    #Do something
    Write-host $Item["Title"]
}

SharePoint CAML for Multiple Choice

Let’s get all projects where the priority, a multichoice column has both “High” and “Medium” values selected.

$Query.ViewXml = "@
<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <And>
                <Contains>
                    <FieldRef Name='Priority' /><Value Type='MultiChoice'>High</Value>
                </Contains>
                <Contains>
                    <FieldRef Name='Priority' /><Value Type='MultiChoice'>Medium</Value>
                </Contains>
            </And>
        </Where>
    </Query>
</View>"

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 *