CAML Query in Powershell Issues

Question

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>

Answer 1

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?

Answer 2

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