SharePoint Online：PowerShell 中使用 IsNull 和 IsNotNull 示例的 CAML 查询

要求：从列表中获取特定字段值为 Null 的所有项目。

SharePoint Online：CAML 查询 IsNull 示例

以下是如何在 CAML 中使用 IsNull 运算符的示例：

#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：在 PowerShell 中使用 IsNotNull 运算符进行 CAML 查询

同样，您可以使用“”运算符来获取特定字段值不为空的列表项。这次，我们使用 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"]
}