CAML Query to Get List Items from SharePoint using PowerShell

CAML stands for Collaborative Application Markup Language. It’s an XML-based query language used to query against data in SharePoint.

How to use CAML query in PowerShell?

We create a CAML query and pass an SPQuery object to retrieve filtered list items from the object. Here is a basic example of using the CAML query in PowerShell – let’s get all “Active” Projects from the projects list.

SharePoint PowerShell CAML Query Example

We can use CAML queries to filter and retrieve data from SharePoint. Here is how to use CAML query in PowerShell:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Set config variables
$WebURL="https://intranet.crescent.com/"
$ListName ="Projects"

#Get Web and List Objects
$Web = Get-SPWeb $WebURL
$List = $Web.Lists[$ListName]

#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.SPQuery
$Query.Query = "@
<Where>
    <Eq>
        <FieldRef Name='Status' />
        <Value Type='Choice'>Active</Value>
    </Eq>
</Where>"

#Get List Items matching the query
$ListItems = $List.GetItems($Query)

Write-host "Total Number of Items: "$ListItems.count

#Loop through Each Item
ForEach($Item in $ListItems) 
{
    #Do something
    Write-host $Item["Title"]
}

Why should we use CAML to filter list items?
Well, we can filter list items as:

$ListItems = $List.Items | Where {$_["Status"] -eq "Active"}

However, Using CAML is highly recommended. Using CAML query to get items from SharePoint list or document library benefits greater performance!

SharePoint CAML Query Builder Tool
There are tools available to generate CAML Query. My favorite CAML query builders are U2U CAML Query Builder and CAML Designer 2013, which work with SharePoint On-premises and SharePoint Online.

Here is my other post for SharePoint Online on How to use CAML query with PowerShell: SharePoint Online CAML Query in PowerShell

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

Your email address will not be published. Required fields are marked *