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 IsNull operator can be used in CAML:
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 CSOM PowerShell script.
SharePoint Online: CAML Query IsNull Example
Here is the example of how IsNull operator can be used in CAML:
#Parameters $SiteURL = "https://crescent.sharepoint.com/sites/marketing/" $ListName= "Documents" $FieldName = "Title" $ReportOutput = "C:\Temp\ListInventory.csv" #Connect to SharePoint Online site Connect-PnPOnline $SiteURL -UseWebLogin #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 }) $ItemCounter++ 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 CSOM PowerShell script.
#Set parameter values $SiteURL="https://crescent.sharepoint.com/sites/marketing" $ListName="Config" $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 = "@ <View> <Query> <Where> <IsNotNull><FieldRef Name='$FieldName' /></IsNotNull> </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.id Write-host $Item["Title"] }
No comments:
Please Login and comment to get your questions answered!