CAML Query using PowerShell

$URL = "yoursiteurl"

$site = New-Object Microsoft.SharePoint.SPSite($URL)

$web = $site.openweb("")

$list=$web.Lists["TestDocs"]

$listname = $list.title

           

           

Write-Host $list.title

$spQuery = New-Object Microsoft.SharePoint.SPQuery

#$camlQuery ="<Where><Or><Eq><FieldRef Name='ContentType' /><Value Type='Computed'>CV</Value></Eq><Eq><FieldRef Name='ContentType' /><Value Type='Computed'>Form FDA 1572</Value></Eq></Or></Where>"

$camlQuery = '<OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy>'

Write-Host $camlQuery

$spQuery.Query = $camlQuery

$spQuery.RowLimit = 100

Write-Host "Query string" $spQuery.Query

$spListItems = $list.GetItems($spQuery)

Write-Host "spListItems :" $spListItems

  foreach ($item in $spListItems)

    {

      write-host "Name:" $item.Name

      write-host "Title:" $item["Title"]

    }

      

CAML Query Issues SharePoint

CAML Builder code

<Query><Where><And><Eq><FieldRef Name="Created" /><Value Type="DateTime">[Today+1Day(s)]</Value></Eq></And></Where></Query>

Remove <Query> node as shown below in the coding

<Where><And><Eq><FieldRef Name="Created" /><Value Type="DateTime">[Today+1Day(s)]</Value></Eq></And></Where>

CAML Builder code

<Value Type="DateTime">[Today+1Day(s)]</Value>

Update as shown below in the coding

Get offsetdays value at runtime

<Value Type='DateTime'><Today OffsetDays='" + offsetdays + "'/></Value>

You can hard code like this

<Today OffsetDays='5'/></Value>

Below format is not supporting some times in the coding

   <Where>

      <And>

         <Eq>

            <FieldRef Name='Created' />

            <Value Type='DateTime'><Today OffsetDays='5'/></Value>

         </Eq>

      </And>

   </Where>

Always use below format in the coding

<Where><And><Eq><FieldRef Name="Created" /><Value Type="DateTime><Today OffsetDays='5'/></Value></Eq></And></Where>

   

Sample caml query function

        private static string CreateCAMLQuery(string symbol,string offsetdays,string redcnt)

        {

            string strQuery = string.Empty;

            switch (symbol.ToLower())

            {

                case "green":

                    strQuery = "<Where><And><IsNull><FieldRef Name='Actual_Date' /></IsNull><Geq><FieldRef Name='Target_Date' /><Value Type='DateTime'><Today OffsetDays='" + offsetdays + "'/></Value></Geq></And></Where>";

                    return strQuery;

                case "yellow":

                    strQuery = "<Where><And><IsNull><FieldRef Name='Actual_Date' /></IsNull><And><Gt><FieldRef Name='Target_Date' /><Value Type='DateTime'><Today OffsetDays='" + redcnt + "'/></Value></Gt><Leq><FieldRef Name='Target_Date' /><Value Type='DateTime'><Today OffsetDays='" + offsetdays + "'/></Value></Leq></And></And></Where>";

                    return strQuery;

                case "red":

                    strQuery = "<Where><And><IsNull><FieldRef Name='Actual_Date' /></IsNull><Leq><FieldRef Name='Target_Date' /><Value Type='DateTime'><Today OffsetDays='" + offsetdays + "'/></Value></Leq></And></Where>";

                    return strQuery;

                default:

                    return null;

            }

        }

Call caml query function

    if (item["Days_x0020_for_x0020_Green"] != null)

                                           {

                                               queryGreen.Query = CreateCAMLQuery("green", item["Days_x0020_for_x0020_Green"].ToString(), "0");

                                               SPListItemCollection itemsGreen = list.GetItems(queryGreen);

                                               drow1[1] = "/_layouts/images/KPIDefault-0.GIF";

                                               drow1[2] = itemsGreen.Count.ToString();

                                           }