Caml Query for retrieving Data

Question

i want to retrieve all data from a table without any condition.

what query should i write for that.

Answer 1

what table? did you mean a document library or list?

In this case, you can simply create a CAML Query with the fields you want to retrieve(select) and dont mention a where clause, you will get all items of document library or list wherever you are running it. a quick example is below

SPQuery query = new SPQuery();

 query.ViewFields = "<FieldRef Name='Field1'/>" +

                      "<FieldRef Name='Field2'/>";   

SPListItemCollection items = mylist.GetItems(query);

---

Moonis Tahir MVP, MCPD, MCSD.net, MCTS BizTalk 2006/SQL 2005/SharePoint Server 2007 (Dev & Config)

Answer 2

Thanks moonis,

i tried this and it has been run.

i have again one questions.

Now i want to retrieve data from two different list using some filter condition

so can I use inner join in caml query.

if not then what are the proposed solutions to this problem?

Answer 3

you can use SPSiteDataQuery object to get data out of one or more lists/document libraries/page libraries etc. you can specify a specific ListIDs to search into OR you can specify a template id that represents like document library , list, calendar etc. here is a little code snippet

           // get the current web content 
            SPWeb oWeb = SPContext.Current.Web;

            // SP data query syntax 
            SPSiteDataQuery oQry = new SPSiteDataQuery();
            oQry.Query = "<OrderBy><FieldRef Name='Title' /></OrderBy>";
            oQry.ViewFields = "<FieldRef Name='Title' />";
            oQry.Lists = "<Lists ServerTemplate='101' />";
            oQry.Webs = "<Web Scope='Recursive' />";

            // query execution return data in a data table
            DataTable oTbl = oWeb.GetSiteData(oQry);

Please note that here List server template 101 means doc library.

you can also specify indivual list ids to target just specific lists like following

<Lists>
  <List ID="1B9FDBW6-0841-410a-8D1L-29355801B5K7" />
  <List ID="3R18F50Q-FSA1-3F1e-B345-9R720DC84FF3" />
</Lists>

---

Moonis Tahir MVP, MCPD, MCSD.net, MCTS BizTalk 2006/SQL 2005/SharePoint Server 2007 (Dev & Config)

Answer 4

A new feature in SharePoint 2010 is SPQuery based joins:

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.joins(office.14).aspx

Using two lists - one called "A" and the other "B", where A has lookups which point to items in B:

<View>
<ViewFields>
<FieldRef Name="IDfromB" />
<FieldRef Name="ID"/>
<FieldRef Name="Title"/>
</ViewFields>
<Joins>
<Join Type="LEFT" ListAlias="Questions">
<Eq>
<FieldRef Name="IDinListB" RefType="ID" />
<FieldRef List="B" Name="ID" />
</Eq>
</Join>
</Joins>
<ProjectedFields>
<Field Name="IDfromB" Type="Lookup" List="B" ShowField="ID" />
</ProjectedFields>
</View>

That would do it for just that one SPQuery. If you want to always do it, you can use projected fields as a part of the definition of your lookup.

Answer 5

thanks Jon for more information on query joins, i missed his inner join question line.

---

Moonis Tahir MVP, MCPD, MCSD.net, MCTS BizTalk 2006/SQL 2005/SharePoint Server 2007 (Dev & Config)

Answer 6

Thanks Jon for giving me update information.

Answer 7

John & Moonis,

Here is the case ...

Soon after you enable "Management Information Policy" on any Document library / List, Sharepoint creates a column "Expiration Date".

Internally, this column is reffered as "_dlc_ExpireDate".

Using CAML, I want to fetch all the items in all the Document Libraries (Template 101) whose "Expiration Date" is between specific date range. Below given CAML / Code doesn't fetch data.

---------------------------

CAML

   <Where>
      <And>
         <Geq>
            <FieldRef Name='_dlc_ExpireDate' />
            <Value Type='DateTime'>[Today+0Day(s)]</Value>
         </Geq>
         <Leq>
            <FieldRef Name='_dlc_ExpireDate' />
            <Value Type='DateTime'>[Today+5Day(s)]</Value>
         </Leq>
      </And>
   </Where>

Code

 SPSiteDataQuery oQry = new SPSiteDataQuery();
 oQry.Query = "****ABOVE CAML*****"
 oQry.ViewFields = "<FieldRef Name='Title' />";
 oQry.Lists = "<Lists ServerTemplate='101' />";
 oQry.Webs = "<Web Scope='Recursive' />";
 DataTable oltb = oWeb.GetSiteData(oQry);

---------------------------

The CAML is generated from U2U tool and that tool does return correct data - so hopefully CAML is correct.

Is this CODE which is not fetching data ? PS: Code works for simple CAML though.

Extended question for CAML (with scope=recursive):   How to fetch "Name" column of each item which comes in the result set (I assume all document library, derived from 101 template, will have "Name" column). Please note that  "<FieldRef Name='Name' /><FieldRef Name='Title' />"; doesn't work.

Thanks,

Sid

Answer 8

Turns out the CAML wasn't appropriate for Web (recursive scope) !

Below CAML worked well ...

<Query>
 <Where>
    <And>
        <Geq>
            <FieldRef Name="_dlc_ExpireDate" />
            <Value Type="DateTime">
                <Today OffsetDays="0" />
            </Value>
        </Geq>
        <Leq>
            <FieldRef Name="_dlc_ExpireDate" />
            <Value Type="DateTime">
                <Today OffsetDays="5" />
            </Value>
        </Leq>
      </And>
    </Where>
</Query>

 

Answer to extended question: Solved it the same day but couldn't get chance to share.

SharePoint keeps internal columns for each List and to be able to access them using CAML you must know  their actual names. FileRef is what I was looking for in my case.

If  someone is looking for list of all the columns (with thier internal as well as display name), then link is here:http://msdn.microsoft.com/en-us/library/aa973474(office.12).aspx

Answer 9

For instance, lets us take the case of s State list which holds all the states of India grouped by different zones i.e. North, South, East and West. We now need to run a query to filter out the states belonging to North and South zone. We would ideally prefer to use an OR condition with the SPQuery to retrieve the required output.

SPQuery stateQuery = new SPQuery();

stateQuery.Query =

"<Where>

<Or>

    <Eq><FieldRef Name=\"Zone\"/><Value Type=\"Text\">North</Value></Eq>

    <Eq><FieldRef Name=\"Zone\"/><Value Type=\"Text\">South</Value></Eq>

</Or>

</Where>";

 

SPListItemCollection stCol = stateList.GetItems(stateQuery);

---

Cheers, Eliza

Answer 10

Hi

Here you find the basics of CAML query tutorial Basics of the CAML query . Also you will find an example to retrieve the data from the SharePoint list in this article CAML Query to Get Data from SharePoint List

Hope thsi helps you!

---

Cheers! Maruthu | http://sharepoint-works.blogspot.com

Answer 11

Check out this

http://www.fewlines4biju.com/2011/04/query-list-by-using-caml-in-sharepoint.html

May be helpful to you.

---

Thanks Bijay | @Fewlines4Biju | LinkedIn | FaceBook | My Blog | SharePointDotNet.com | EnjoySharePoint.com