SharePoint Online: consulta CAML con ejemplos IsNull y IsNotNull en PowerShell

SharePoint Online: consulta CAML con ejemplos IsNull y IsNotNull en PowerShell

Requisito: Obtener todos los elementos de una lista en la que un valor archivado particular sea Nulo.

SharePoint Online: Ejemplo de consulta CAML IsNull

Este es el ejemplo de cómo se puede usar el operador IsNull en 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 -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     })     $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: consulta CAML con el operador IsNotNull en PowerShell

De manera similar, puede usar el operador «» para obtener elementos de la lista donde un valor de campo en particular no es nulo. Esta vez, usemos el script CSOM PowerShell.

 #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"] }