SharePoint Online：PowerShell 中查找字段的 CAML 查询

要求：使用 PowerShell 对 SharePoint Online 中的“查找”字段进行 CAML 查询。

用于 SharePoint 查找字段值的 CAML：

SharePoint 中的查找字段允许用户从另一个列表中选择一个值，然后将其链接到当前列表项。查找字段用于创建两个列表之间的关系。 CAML（协作应用程序标记语言）是一种基于 XML 的查询语言，在 SharePoint 中用于查询列表和库中的数据。在本文中，我们将了解如何在 PowerShell 中为查找字段创建 CAML 查询。

让我们通过父项目查找字段值“通讯录”来过滤项目。以下是查找字段的 SharePoint CAML。

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
 
#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
$LookupValue="Address Book"
 
#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
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='ParentProject'/><Value Type='Lookup'>$LookupValue</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.id
    Write-host $Item["Title"]
}

SharePoint CAML 查询按 ID 筛选查找字段：

由于父列表中定义的查找文本可能随时更改，因此我们使用查找字段 ID 而不是文本。以下是查找列的 PowerShell CAML 查询。

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
$LookupID="25" #ID of the Parent Lookup Item
 
#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
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='ParentProject' LookupId='TRUE'/><Value Type='Lookup'>$LookupID</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 Online CAML：

以下是多重查找列的 SharePoint CAML 查询，该查询会筛选“父项目”查找字段值同时包含“地址簿”和“公告”的位置。

#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
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <And>
                <Includes>
                    <FieldRef Name='ParentProject'/>
                    <Value Type='LookupMulti'>Address Book</Value>
                </Includes>
                <Includes>
                    <FieldRef Name='ParentProject'/>
                    <Value Type='LookupMulti'>Announcements</Value>
                </Includes>
            </And>
        </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"]
}