HOW TO USE CAML QUERY IN SHAREPOINT TO FETCH LIST ITEMS

 By: Unknown    01:43     No comments

CAML represents Collaborative Markup Language which is an XML based language for querying and updating SharePoint objects.

CAML Query Comparison Operators:

Tag Name	Meaning
Contains	Contains a given text value
Eq	Equal to
Geq	Greater than or equal to
Gt	Greater than
Leq	Less than or equal to
Lt	Less than
Neq	Not equal to
DateRanges Overlap	Compares dates in recurring events to determine if they overlap
IsNotNull	Is not null
IsNull	Is null

Examples:-

 FETCH CURRENT USER ITEMS FROM SHAREPOINT  LIST USING CAML QUERY

 Dim userName  As SPUser = SPContext.Current.Site.Web.Currentuser.name

  Using site As SPSite = SPContext.Current.Site

  Using _web As SPWeb = site.OpenWeb()

    Dim _waitinglist As SPList = _web.Lists("List Name")

    Dim oSb As New System.Text.StringBuilder

           oSb.Append("         <OrderBy>")

            oSb.Append("              <FieldRef Name=""Priority""  Ascending='FALSE'/>")

            oSb.Append("         </OrderBy>")

            oSb.Append("         <Where>")

            oSb.Append("                       <Eq>")

            oSb.Append("                             <FieldRef Name=""Booked_x0020_By"" />")

            oSb.Append("                             <Value Type=""User"">" & userName  & "</Value>")

            oSb.Append("                        </Eq>")

            oSb.Append("         </Where>")

    Dim sResult As String = oSb.ToString()

    Dim query As New SPQuery()

    query.Query = sResult

    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           

 End Using

 End Using

In the above code Booked by is people picker column in sharepoint list

 FETCH  SHAREPOINT  LIST  ITEM'S BETWEEN TO TWO  DATES

  Using site As SPSite = SPContext.Current.Site
  Using _web As SPWeb = site.OpenWeb()
  Dim _waitinglist As SPList = _web.Lists("List Name")
    Dim oSb As New System.Text.StringBuilder
    oSb.Append("  <Where>")
    oSb.Append("     <And>")
    oSb.Append("       <Lt>")
    oSb.Append("          <FieldRef Name=""Start_x0020_Date_x0020_Time"" />")
    oSb.Append("           <Value Type=""DateTime"" IncludeTimeValue=""TRUE"">" & enddatetime & "</Value>")
    oSb.Append("       </Lt>")
    oSb.Append("        <Gt>")
    oSb.Append("            <FieldRef Name=""End_x0020_Date_x0020_Time"" />")
    oSb.Append("            <Value Type=""DateTime"" IncludeTimeValue=""TRUE"">" & startdatetime & "</Value>")
    oSb.Append("        </Gt>")
    oSb.Append("     </And>")
    oSb.Append("   </Where>")
    Dim sResult As String = oSb.ToString()
    Dim query As New SPQuery()
    query.Query = sResult
    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           
 End Using
 End Using

 FETCH THE SHAREPOINT LIST ITEMS BY COMPARING BOOLEAN VALUE
      
        Dim web As SPWeb = Common.getWeb
        web.AllowUnsafeUpdates = True
        Dim _list As SPList = web.Lists("RESOURCE")
        Dim query As New SPQuery
        Dim oSb As New System.Text.StringBuilder
        oSb.Append("         <Where>")
        oSb.Append("                   <Eq>")
        oSb.Append("                        <FieldRef Name=""Displayed"" />")
        oSb.Append("                        <Value Type=""Boolean"">1</Value>")
        oSb.Append("                   </Eq>")
        oSb.Append("         </Where>")
        Dim sResult As String = oSb.ToString()
        query.Query = sResult
        Dim Items As SPListItemCollection = _list.GetItems(query)
        Return Items

USING CAML TO QUERY SHAREPOINT LISTS OVER LOOKUP FIELDS

Using site As SPSite = SPContext.Current.Site

  Using _web As SPWeb = site.OpenWeb()

    Dim _waitinglist As SPList = _web.Lists("List Name")

    Dim oSb As New System.Text.StringBuilder

    oSb.Append("  <Where>")

    oSb.Append("      <Eq> ")

    oSb.Append("          <FieldRef Name=""ProjectID"" />")

    oSb.Append("           <Value Type="Lookup">My Test Project 1</Value> 

    oSb.Append("       </Eq> ")

    oSb.Append("   </Where>")

    Dim sResult As String = oSb.ToString()

    Dim query As New SPQuery()

    query.Query = sResult

    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           

 End Using

 End Using

CAML QUERY TO MATCH TEXT FIELD IN SHAREPOINT LSIT

 Using site As SPSite = SPContext.Current.Site

  Using _web As SPWeb = site.OpenWeb()

    Dim _waitinglist As SPList = _web.Lists("List Name")

    Dim oSb As New System.Text.StringBuilder

    oSb.Append("  <Where>")

    oSb.Append("      <Eq> ")

    oSb.Append("          <FieldRef Name=""Designation"" />")

    oSb.Append("           <Value Type="Text">Enginee</Value> 

    oSb.Append("       </Eq> ")

    oSb.Append("   </Where>")

    Dim sResult As String = oSb.ToString()

    Dim query As New SPQuery()

    query.Query = sResult

    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           

 End Using

 End Using

HOW TO USE INCLUDE OPERATOR IN CAML QUERY

INCLUDES:

MSDN Def: If the specified field is a Lookup field that allows multiple values,

specifies that the Value element is included in the list item for the field that is specified by the FieldRef element.

  Using site As SPSite = SPContext.Current.Site

  Using _web As SPWeb = site.OpenWeb()

    Dim _waitinglist As SPList = _web.Lists("List Name")

    Dim oSb As New System.Text.StringBuilder

    oSb.Append("  <Where>")

    oSb.Append("      <Includes> ")

    oSb.Append("          <FieldRef Name=""Organization"" />")

    oSb.Append("           <Value Type="Lookup">microsoft</Value> 

    oSb.Append("       </Includes> ")

    oSb.Append("      <Includes> ")

    oSb.Append("          <FieldRef Name=""Designation"" />")

    oSb.Append("           <Value Type="Lookup">.NetDeveloper</Value> 

    oSb.Append("       </Includes> ")

    oSb.Append("   </Where>")

    Dim sResult As String = oSb.ToString()

    Dim query As New SPQuery()

    query.Query = sResult

    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           

 End Using

 End Using

HOW TO USE NOT-INCLUDE OPERATOR IN CAML QUERY

Not-Include quite oppsite to Include operator

 Using site As SPSite = SPContext.Current.Site
  Using _web As SPWeb = site.OpenWeb()
    Dim _waitinglist As SPList = _web.Lists("List Name")
    Dim oSb As New System.Text.StringBuilder
    oSb.Append("  <Where>")
    oSb.Append("      <Includes> ")
    oSb.Append("          <FieldRef Name=""Organization"" />")
    oSb.Append("           <Value Type="Lookup">microsoft</Value> 
    oSb.Append("       </Includes> ")
    oSb.Append("      <NotIncludes> ")
    oSb.Append("          <FieldRef Name=""Designation"" />")
    oSb.Append("           <Value Type="Lookup">.NetDeveloper</Value> 
    oSb.Append("       </NotIncludes> ")
    oSb.Append("   </Where>")
    Dim sResult As String = oSb.ToString()
    Dim query As New SPQuery()
    query.Query = sResult
    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           
 End Using
 End Using