CAML Query

-

CAML Query tutorial for SharePoint

In this article, we will understand the basics of CAML query in SharePoint.
What is CAML?
  Ø  CAML - Collaborative Application Markup Language
  Ø  XML- Extensible Markup Language based query language
  Ø  Used to perform a query operation against SharePoint Lists
How SharePoint List Items are retrieved?
SharePoint List data can be retrieved in any one of the following ways:
1. Using the SharePoint object model – used when code runs on the server         (Example: Developing a web part or an application page)
2. Using the SharePoint Lists web service – used when your code doesn’t run on the server where the SharePoint is installed (Example: Developing a windows application)
3. Using Power shell –used mostly by the ADMIN of the SharePoint when they quickly want to retrieve some information from the SharePoint site
How does CAML query looks like?
As I already mentioned, it is XML based query language and it contains tags in it. The root element of the CAML query root element is Query. But it is not necessary to use Query element in the query you form.
Within the Query element you have two elements possible:
1. Where   – to filter the data
2. OrderBy – to categorize the data
A simple structure of the CAML query is as follows:
<Query>
          <Where>
                   <Eq>
                             <FieldRef Name=”FieldName” />
                             <Value Type=”DataType”>Value</Value>
                   </Eq>
          </Where>
          <OrderBy>
                             <FieldRef Name=”FieldName” />
                             <FieldRef Name=”FieldName” />
          </OrderBy>
</Query>
Operators in CAML Query
From the above structure, we came to know that it uses Where and OrderBy elements to retrieve the data from the list.
Let us know about the operators present in the CAML query and its usage:
Inside the Where element
1. Logical Operators - AND, OR
2. Comparison Operators - Listed Below in the table
AND – Which takes two conditions are satisfied
OR – Which takes when either of the conditions is satisfied
Comparison Operators
Inside the OrderBy/GroupBy element
OrderBy – Which orders or sort the data depends upon the field (FieldRef element) given.
GroupBy – Which groups the data depends upon the group by field (FieldRef element) given.
Examples
Logical & Comparison Operators
Use of AND, Gt, Leq
<Query>
<Where>
<And>
<Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
</Gt>
<Leq>
<FieldRef Name="Price" />
<Value Type="Number">2000</Value>
</Leq>
</And>
</Where>
</Query>
Use of OR, Gt, Leq
<Query>
<Where>
<Or>
     <Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
     </Gt>
                     <Leq>
  <FieldRef Name="Price" />
<Value Type="Number">2000</Value>
     </Leq>
               </Or>
       </Where>
</Query>
Use of BeginsWith, Leq
<Query>
<Where>
<And>
      <BeginsWith>
  <FieldRef Name="Title" />
  <Value Type="Text">M</Value>
      </BeginsWith>
      <Leq>
     <FieldRef Name="Quantity" />
<Value Type="Number">1000</Value>
      </Leq>
</And>
</Where>
<OrderBy>
<FieldRef Name="Price" Ascending="False" />
</OrderBy>
</Query>
OrderBy Operator
<Query>
<Where>
<Or>
    <Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
   </Gt>
      <Leq>
<FieldRef Name="Price" />
<Value Type="Number">2000</Value>
   </Leq>
</Or>
</Where>
<OrderBy>
<FieldRef Name="Price" Ascending="True" />
</OrderBy>
</Query>
Is it possible to write this queries without any errors manually?
Yes. But we will know the errors only after executing the program. Hence there is a freeCAML query builder which will generate the query easily.
To know about where to download and how to use the CAML Query builder read thisarticle.

Comments

Post a Comment

Popular posts from this blog

what is Event Cache table in sharepoint

-
Every Content database in SharePoint Contains an EventCache table that is the change log for objects contained in the database. Each row in this table is depicting a change in an object where columns usually contain information such as the date and time of a change, the type of object that was changed, the nature of the change, and a unique identifier for the object. EventCache table is important because it stores the list of site events for which users might have requested alerts.
Read more

CAML Query syntax and options in SharePoint

-
As Small syntax error or order of tag in spquery can result any web part (or any thing in which spquery is used) doesn’t work, I’m explaining SPQuery with example, hope which may help you. CAML (Collaborative Application Markup Language) SPQuery  is the SharePoint object which is used to perform a query operation against SharePoint data. SPList.getItems(SPQuery)  is the step, will return SPListItemCollection  which satisfies the query.SPQuery has one data member ‘ Query’ , which need to set before passing SPQuery object to  SPList . Some Properties Properties Description RowLimit Gets or sets a limit for the number of items returned in the query per page. <RowLimit>10</RowLimit> --- Can be used as: query.rowLimit = 10; ViewFields Gets or sets the fields that are displayed in the query. <ViewFields>         <FieldRef Name='Title'/>         <FieldRef Name='Name'/> </ViewFields> --- Can be used as: SP
Read more

Change anchor link url in sharepoint calender

-
here am unsing sharepoint calender to display events. The calender list is inhereted from event content type. for every event there are several columns like event name, event link(URL), Event Date...etc. for some events there may be some url in link column some may be empty. So in calender if i click the event, suppose if that event has the LINK with url then i have to redirect to the new url, if the link url is empty then i have to redirect to the event url. here when am clilcking on anchor tag in calender am calling a rest api function to get the link value of that event. am checking the link value , if value in null then am redirecting to event details and the value is not null then am redirecting to the link which am get from rest api call. here am using two mail functions which are 1)e.stopPropagation(); means it will stop the redirecting value which is assigned initially. 2)e.preventDefault(); means it will stop the default value of the anchor tag href value.
Read more