SharePoint Online: CAML Query with IsNull and IsNotNull Examples in PowerShell

Requirement: Get all items from a list where a particular filed value is Null.

SharePoint Online: CAML Query IsNull Example

Here is the example of how the IsNull operator can be used in CAML:

$SiteURL = ""
$ListName= "Documents"
$FieldName = "Title"
$ReportOutput = "C:\Temp\ListInventory.csv"
#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -Interactive
#Array to store results
$Results = @()
#Define CAML Query to get items where the field value is null
$CAMLQuery = "<View><Query><Where><IsNull><FieldRef Name='$FieldName' /></IsNull></Where></Query></View>"
#Get all Items filtered by CAML query
$ListItems = Get-PnPListItem -List $ListName -Query $CAMLQuery -PageSize 1000
Write-host "Total Number of Items Found:"$ListItems.Count

$ItemCounter = 0 
#Iterate through each item
Foreach ($Item in $ListItems)
    #get the Field Values
    $Results += New-Object PSObject -Property ([ordered]@{
        Name              = $Item.FieldValues.FileLeafRef
        Type              = $Item.FileSystemObjectType
        FileType          = $Item.FieldValues.File_x0020_Type
        RelativeURL       = $Item.FieldValues.FileRef
        CreatedBy         = $Item["Author"].Email
    Write-Progress -PercentComplete ($ItemCounter / ($List.ItemCount) * 100) -Activity "Processing Items $ItemCounter of $($List.ItemCount)" -Status "Getting Metadata from Item '$($Item['FileLeafRef'])"          

#Export the results to CSV
$Results | Export-Csv -Path $ReportOutput -NoTypeInformation
Write-host "Document Library Inventory Exported to CSV Successfully!"

SharePoint Online: CAML query with IsNotNull Operator in PowerShell

Similarly, You can use “<IsNotNull>” Operator to get list items where a particular field value is not null. This time, let us use the CSOM PowerShell script.

#Set parameter values
$FieldName = "description" #Internal Name
#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 = "@
            <IsNotNull><FieldRef Name='$FieldName' /></IsNotNull>
#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
Write-host "Total Number of Items:"$ListItems.count
#Loop through each List Item
ForEach($Item in $ListItems)
    #Do Something
    Write-host $
    Write-host $Item["Title"]

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

Leave a Reply

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