1

I have a powershell script that I am running that executes a caml query to get some list items. The problem I am having is that whenever I execute this query in CAML Builder I am only getting 3 results (what I would expect). However using this same query in my powershell I am getting back all the documents (47 in this case).

I am pretty sure It has something to do with my powershell code but can't seem to figure it out.

Here is my code:

$SiteCollectionUrl ="http://sharepointsite/site"


$RootWeb = get-spweb $SiteCollectionUrl

foreach ($list in ($RootWeb.Lists | ? {$_.BaseTemplate.ToString() -eq "DocumentLibrary"})) 

    {
#######################################
#Build Caml Query
#######################################
            $spQuery = New-Object Microsoft.SharePoint.SPQuery 

            $query =    '<Query><Where><And><IsNull><FieldRef Name="_vti_ItemDeclaredRecord" /></IsNull><IsNotNull><FieldRef Name="CheckoutUser" /></IsNotNull></And></Where></Query>'

            $spQuery.ViewAttributes = "Scope = 'Recursive'"
            $spQuery.Query = $query
            $spQuery.RowLimit = $list.ItemCount
            $spListItemCol = $list.GetItems($spQuery)
            Write-Host $spListItemCol.Count


                foreach($i in $spListItemCol)
                {
                    #Process Items                  
                }
}

Here is the caml query that I use in the builder:

<Query>
<Where>
<And>
<IsNull><FieldRef Name="_vti_ItemDeclaredRecord" /></IsNull>
<IsNotNull><FieldRef Name="CheckoutUser" /></IsNotNull>
</And>
</Where>
</Query>
3

I don't know how I missed the answer to this before, but I had to remove the <query> and </query> from the $query string.

However I now noticed that this works on some libraries and not others?

0

this code working for caml query with powershell

param(
    [string]$siteCollUrl = "http://test/sites/site1/",
    [string]$listTitle="Stockage", 
    [boolean]$checkin=$false,
    [boolean]$publish=$false, 
    [boolean]$deleteAllVersion=$true
)
$web = Get-SPWeb $siteCollUrl
$splList = $web.Lists[$listTitle]            
$spqQuery = New-Object Microsoft.SharePoint.SPQuery
$spqQuery.Query = "<Where><Neq><FieldRef Name='_Level' /><Value Type='Number'>1</Value></Neq></Where><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>"; 

$spqQuery.ViewAttributes = "Scope=""RecursiveAll""";
$spqQuery.ViewFields = "<FieldRef Name='FileLeafRef' /><FieldRef Name='Title' /><FieldRef Name='ID' /><FieldRef Name='owshiddenversion' /><FieldRef Name='_Level' /><FieldRef Name='_IsCurrentVersion' /><FieldRef Name='_UIVersion' />" #_UIVersionString
$spqQuery.ViewFieldsOnly = $true 
$spqQuery.RowLimit = 1000;

# IsCheckedoutToLocal
    $iNumber=1 
do
{
    if($iNumber -eq 1)
    {
        $spqQuery.ListItemCollectionPosition = $splListItems.ListItemCollectionPosition;
    }
    $splListItems = $splList.GetItems($spqQuery) 
    writeInfo "page number : $($iNumber)" 
    foreach ($splListItem in $splListItems) 
    { 
        #do your job
    }
    $iNumber+=1 
    $spqQuery.ListItemCollectionPosition = $splListItems.ListItemCollectionPosition;
}
while($spqQuery.ListItemCollectionPosition -ne $null) 

$web.Dispose()

get script here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.