I'm trying to understand why the CAML I'm using isn't working with the Sharepoint PNP Commands. These commands work in my query builder and I've used them in the past with older PowerShell code so I'm fairly confident something should work.
I'm trying to recurs directories and find files. It seems like no matter what I do with Get-PnPListItem it doesn't honor all the markup. I can't even get it to find a title properly. It always finds folders. I tried <View Scope='FileOnly' /> but it is ignored. If I use RecuresALL I get all and the query is ignored.
It is so hard to find examples and proper docs on it. It was hard to figure out where to go for PNP Questions.
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.
-
PnP PowerShell
-
CLI for Microsoft 365 with PowerShell
#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>
<Geq><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
#Global Variable Declaration
$SiteURL = "https://tenant.sharepoint.com/sites/SiteName/"
$ListName = "ListName"
Function loginToTenant() {
$m365Status = m365 status
if ($m365Status -eq "Logged Out") {
m365 login
}
}
Function FilterTextField() {
Write-Host "**************** FILTER BY TEXT **************** " -ForegroundColor Green
$FilterTextField = m365 spo listitem list --title $ListName --webUrl $SiteURL --camlQuery "@<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 = m365 spo listitem list --title $ListName --webUrl $SiteURL --camlQuery "@<View>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='GUID'/>
<FieldRef Name='Modified'/>
</ViewFields>
<Query><Where>
<Geq><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 = m365 spo listitem list --title $ListName --webUrl $SiteURL --camlQuery "@<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 = m365 spo listitem list --title $ListName --webUrl $SiteURL --camlQuery "@<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 = m365 spo listitem list --title $ListName --webUrl $SiteURL --camlQuery "@<View>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='User'/>
<FieldRef Name='GUID'/>
</ViewFields>
<Query>
<Where>
<Eq>
<FieldRef Name='User'/>
<Value Type='User'>Adam Wójcik</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 = m365 spo listitem list --title $ListName --webUrl $SiteURL --camlQuery "@<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 = m365 spo listitem list --title $ListName --webUrl $SiteURL --camlQuery "@<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() {
loginToTenant
GetListItemsUsingCAML
}
Main
Contributors
| Author(s) |
| Chandani Prajapati |
| Adam Wójcik |
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.
