(CAML Query 문법) syntax and options in SharePoint

2014.02.04 17:38 개발언어/SharePoint
(CAML Query 문법) syntax and options in SharePoint

참고사이트 : http://www.a2zdotnet.com/View.aspx?Id=90#.UvCF7vl_vxE 


 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:
 SPQuery query = new SPQuery();
 query.ViewFields = "<FieldRef Name='Field1'/>" +
                      "<FieldRef Name='Field2'/>";   

How to Implement SPQuery?

How to Implement SPQuery?

using (SPWeb web = SPContext.Current.Site.RootWeb)
{
    SPList mylist = web.Lists["Tasks"];
    SPQuery query = new SPQuery();
    query.Query = "<Where><Eq><FieldRef Name='Status'/>" +
                   "<Value Type='Text'>Completed</Value></Eq></Where>";
    SPListItemCollection items = mylist.GetItems(query);
    foreach (SPListItem item in items)
    {
        Response.Write(SPEncode.HtmlEncode(item["Title"].ToString()) + "<BR>");
    }
}

Operators:

Comparison Operators, Logical Joins and Order/ Group Operators, plays an important role to make this syntax.

 

Comparison Operators

Comparison Operators

General Meaning

Eq

=

Gt

> 

Lt

< 

Geq

>=

Leq

<=

Neq

<> 

Contains

Like

IsNull

Null

IsNotNull

NotNull

BeginsWith

Beginning with word

DateRangesOverlap

compare the dates in a recurring event with a specified DateTime value, to determine whether they overlap

 

Now, to use Or between Geq and Leq conditions, we put it inside <Or> tag.

I.e. condition: where (Field1 >= 1500) or (field2 <= 500) can be written like this:

   <Where>
  <Or>
     <Geq>
         <FieldRef Name='Field1'/>
         <Value Type='Number'>1500</Value>
     </Geq>
     <Leq>
        <FieldRef Name='Field2'/><Value Type='Number'>500</Value>
     </Leq>
   </Or>
  </Where>

The full Code snippets will look like:

<View>
 <Query>
      <OrderBy>
          <FieldRef Name='ID'/>
      </OrderBy>
 
  <Where>
  <Or>
     <Geq>
         <FieldRef Name='Field1'/>
         <Value Type='Number'>1500</Value>
     </Geq>
     <Leq>
        <FieldRef Name='Field2'/><Value Type='Number'>500</Value>
     </Leq>
   </Or>
  </Where>
 </Query>
   <ViewFields>
        <FieldRef Name='Title'/>
        <FieldRef Name='Name'/>
   </ViewFields>
   <RowLimit>10</RowLimit>
</View>

Example: Contains, And, BeginsWith

The following example uses the Contains element that is assigned to the Query property to return the titles of items where the Conference column value begins with "Morning" and contains "discussion session".

<Where>
   <And>
       <BeginsWith>
              <FieldRef Name="Conference"/> 
              <Value Type="Note">Morning</Value>
       </BeginsWith>
       <Contains>
              <FieldRef Name="Conference" />
              <Value Type="Note">discussion session</Value>
       </Contains>
   </And>
</Where>

 

Example: DateRangesOverlap

<Where> 
    
<DateRangesOverlap>
          <FieldRef Name="EventDate"></FieldRef>
          <FieldRef Name="EndDate"></FieldRef>
          <FieldRef Name="RecurrenceID"></FieldRef>
            <Value Type="DateTime">
              <Now/>
            </Value>
    
</DateRangesOverlap>
</Where>

Logical Joins:

Logical Joins

Comments

And

Used within the ‘Where’ element to group filters in a query for a view

Or

Used within the ‘Where’ element to group filters in a query for a view

Example: And

<Where>
  
<And>
     <Neq>
        <FieldRef Name="Status"></FieldRef>
        <Value Type="Text">Completed</Value>
     </Neq>
     <IsNull>
        <FieldRef Name="Sent"></FieldRef>
     </IsNull>
  
</And>
</Where>

Order/Group Operators:

Order/Group Operators

Comments

OrderBy

Determines the sort order for a query. The OrderBy element contains a group of FieldRef elements

I.e. <OrderBy><FieldRef Name="Title" Ascending="TRUE">

</FieldRef></OrderBy>

 

GroupBy

Contains a Group By section for grouping the data returned through a query in a list view

Example: OrderBy

<OrderBy> 
       <FieldRef Name="Modified" Ascending="FALSE"></FieldRef>
</OrderBy>
<Where>
  <Or>
    <Neq>
      <FieldRef Name="Status"></FieldRef>
      <Value Type="Text">Completed</Value>
    </Neq>
    <IsNull>
      <FieldRef Name="Status"></FieldRef>
    </IsNull>
  </Or>
</Where>

Example: GroupBy

<GroupBy> 
      <FieldRef Name="Modified"/>
</GroupBy>
<Where>
   <Or>
      <Neq>
          <FieldRef Name="Status"></FieldRef>
          <Value Type="Text">Completed</Value>
      </Neq>
      <IsNull>
          <FieldRef Name="Status"></FieldRef>
      </IsNull>
   </Or>
</Where>

Important:

This is very important thing to keep in mind, because SPQuery will be assigned as a string so it wont show compile time error though it may have tag syntax error, not in order, escape sequence error, etc.

Escape sequence:

The ScriptEncode method escapes characters that would otherwise conflict with script.

Original

Replacement

"

\"

\

\\

+

\u002b

> 

\u003e

< 

\u003c

'

\u0027

 


 

Example:

<FieldRef Name=\"Checkbox\"></FieldRef> <Value Type=\"bit\">1</Value> 

Thanks

신고