Read SharePoint List Items Using CAML Query
Summary
Sometimes we want to read SharePoint list items using CAML query.
Implementation
- Open Windows PowerShell ISE
- Create a new file
- Write a script as below,
- First, we will connect to Tenant admin site.
- Then we will connect to SharePoint site in which we want to read list items.
- And then create a separate functions for all the field operations.
#Global Variable Declaration
$AdminURL = "https://tenant-admin.sharepoint.com/"
$SiteURL = "https://tenant.sharepoint.com/sites/SiteName/"
$ListName = "ListName"
$UserName = "USERNAME"
$Password = "********"
$SecureStringPwd = $Password | ConvertTo-SecureString -AsPlainText -Force
$Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList $UserName, $SecureStringPwd
Function LoginToAdminSite() {
[cmdletbinding()]
param([parameter(Mandatory = $true, ValueFromPipeline = $true)] $Credentials)
Write-Host "Connecting to Tenant Admin Site '$($AdminURL)'..." -ForegroundColor Yellow
Connect-PnPOnline -Url $AdminURL -Credentials $Credentials
Write-Host "Connection Successfull to Tenant Admin Site :'$($AdminURL)'" -ForegroundColor Green
}
Function ConnectToSPSite() {
try {
Write-Host "Connecting to Site :'$($SiteUrl)'..." -ForegroundColor Yellow
Connect-PnPOnline -Url $SiteUrl -Credentials $Credentials
Write-Host "Connection Successfull to site: '$($SiteUrl)'" -ForegroundColor Green
GetListItemsUsingCAML
}
catch {
Write-Host "Error in connecting to Site:'$($SiteUrl)'" $_.Exception.Message -ForegroundColor Red
}
}
Function FilterTextField() {
Write-Host "**************** FILTER BY TEXT **************** " -ForegroundColor Green
$FilterTextField = Get-PnPListItem -List $ListName -Query "@<View>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='GUID'/>
</ViewFields>
<Query>
<Where>
<Eq>
<FieldRef Name='Title'/>
<Value Type='Text'>Ankit</Value>
</Eq>
</Where>
</Query>
</View>"
if ($FilterTextField -eq $null) {
Write-Host "No records found for filter by text" -ForegroundColor Gray
}
else {
$FilterTextField | Format-Table
}
}
Function FilterDateField() {
Write-Host "**************** FILTER BY DATE **************** " -ForegroundColor Green
$FilterDateField = Get-PnPListItem -List $ListName -Query "@<View>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='GUID'/>
<FieldRef Name='Modified'/>
</ViewFields>
<Query><Where>
<Eq><FieldRef Name='Modified'/><Value Type='DateTime'><Today/></Value></Eq></Where>
</Query>
</View>"
if ($FilterDateField -eq $null) {
Write-Host "No records found for filter by date" -ForegroundColor Gray
}
else {
$FilterDateField | Format-Table
}
}
Function FilterChoiceField() {
Write-Host "**************** FILTER BY CHOICE **************** " -ForegroundColor Green
$FilterChoiceField = Get-PnPListItem -List $ListName -Query "@<View>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='Hobby'/>
<FieldRef Name='GUID'/>
</ViewFields>
<Query>
<Where>
<Eq>
<FieldRef Name='Hobby'/>
<Value Type='Choice'>Drawing</Value>
</Eq>
</Where>
</Query>
</View>"
if ($FilterChoiceField -eq $null) {
Write-Host "No records found for filter by text" -ForegroundColor Gray
}
else {
$FilterChoiceField | Format-Table
}
}
Function FilterLookupField() {
Write-Host "**************** FILTER BY LOOKUP **************** " -ForegroundColor Green
$FilterLookupField = Get-PnPListItem -List $ListName -Query "@<View>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='Country'/>
<FieldRef Name='GUID'/>
</ViewFields>
<Query>
<Where>
<Eq>
<FieldRef Name='Country'/>
<Value Type='Lookup'>India</Value>
</Eq>
</Where>
</Query>
</View>"
if ($FilterLookupField -eq $null) {
Write-Host "No records found for filter by text" -ForegroundColor Gray
}
else {
$FilterLookupField | Format-Table
}
}
Function FilterUserField() {
Write-Host "**************** FILTER BY PEOPLE **************** " -ForegroundColor Green
$filterByPeople = Get-PnPListItem -List $ListName -Query "@<View>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='User'/>
<FieldRef Name='GUID'/>
</ViewFields>
<Query>
<Where>
<Eq>
<FieldRef Name='User'/>
<Value Type='User'>Chandani Prajapati</Value>
</Eq>
</Where>
</Query>
</View>"
if ($filterByPeople -eq $null) {
Write-Host "No records found for filter by text" -ForegroundColor Gray
}
else {
$filterByPeople | Format-Table
}
}
Function BeginsWith() {
Write-Host "**************** FILTER BY TEXT BEGINS WITH **************** " -ForegroundColor Green
$FilterByTextBeginsWith = Get-PnPListItem -List $ListName -Query "@<View>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='GUID'/>
</ViewFields>
<Query>
<Where>
<BeginsWith>
<FieldRef Name='Title'/>
<Value Type='Text'>Emp</Value>
</BeginsWith>
</Where>
</Query>
</View>"
if ($FilterByTextBeginsWith -eq $null) {
Write-Host "No records found for filter by choice" -ForegroundColor Gray
}
else {
$FilterByTextBeginsWith | Format-Table
}
}
Function ContainsValue() {
Write-Host "**************** FILTER BY TEXT CONTAINS VALUE **************** " -ForegroundColor Green
$FilterByTextContainsVal = Get-PnPListItem -List $ListName -Query "@<View>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='GUID'/>
</ViewFields>
<Query>
<Where>
<Contains>
<FieldRef Name='Title'/>
<Value Type='Text'>i</Value>
</Contains>
</Where>
</Query>
</View>"
if ($FilterByTextContainsVal -eq $null) {
Write-Host "No records found for filter by contains" -ForegroundColor Gray
}
else {
$FilterByTextContainsVal | Format-Table
}
}
Function GetListItemsUsingCAML() {
FilterTextField
FilterDateField
FilterChoiceField
FilterLookupField
FilterUserField
BeginsWith
ContainsValue
}
Function Main() {
LoginToAdminSite($Credentials);
ConnectToSPSite
}
Main
Check out the PnP PowerShell to learn more at: https://aka.ms/pnp/powershell
The way you login into PnP PowerShell has changed please read PnP Management Shell EntraID app is deleted : what should I do ?
Contributors
| Author(s) |
|---|
| Chandani Prajapati |
| Adam Wójcik |
| Jago Pauwels |
Disclaimer
THESE SAMPLES ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.