Writing CAML Queries For Retrieving List Items from a SharePoint List1
来源:互联网 发布:铣床宏程序编程实例 编辑:程序博客网 时间:2024/06/05 17:27
The Collaborative Application Markup Language (better known as CAML) is an XML-based query language that helps you querying, building and customizing Web sites based on Windows SharePoint Services. The XML elements define various aspects of a WSS site. In this first series of articles I will explain in detail how to build and execute CAML queries to retrieve list items from a SharePoint list. List items can be retrieved in several different ways: using the SharePoint object model, using the SharePoint Lists web service or even by using Powershell. Use the SharePoint object model when your code runs on the server (like f.e. when you’re developing a web part or an application page). Use the SharePoint Web Services when your code doesn’t run on the server where SharePointis installed, for example when you develop office clients or windows applications. Powershell, at the other side, can be used by administrators when they quickly need to retrieve some information. In any way the CAML query is the same. CAML is an XML-based query language. Its root element is Query. Within the Query element two other elements are possible but not required: the OrderBy element and the Where element. The OrderBy element is the simplest one. It is used to sort the returning list items. You have to specify the field(s) on which you want to sort the items and the sort direction. The syntax looks as follows: The OrderBy clause is not required and you can specify one or more fields on which you want to sort. If you omit the Ascending attribute, your resulting rows will be sorted in ascending order. If you want to order in descending order you have to specify Ascending=’False’. The Where clause is used to specify one or more filter criteria. This clause can be very simple but can end up being rather complex. In its most simple form you specify an operator, a field name for which you want to specify a criterion, and a value. You have different operators: Eq Equals Neq Not equal Gt Greater than Geq Greater than or equal Lt Lower than Leq Lower than or equal too IsNull Is null BeginsWith Begins with Contains Contains The FieldRef element can be any field of the list on which you want to execute the CAML query. If you use the Name attribute you need to specify the internal name of the field. But you can also use the IDattribute to specify the Guid of the field. The Valueelement specifies the value part of the criterion. The attribute Typeis optional and specifies the data type of the field you want to specify the criterion for. If omitted the data type is considered as beingText. In all other cases you have to specify the Type attribute. DateTime fields are a special case and will be described in more detail later in this article. If the field type is a Lookup you need to specify the text value.For example you have an Employees list and the Country field is a lookup field referring to the Countries list. In that case an employee living in Belgium will have f.e. following value: #15;Belgium. If you have to query for the employees living in Belgium you will have to write your query as follows: You can find this not good coding practice because the name of the country can change in time. In that case you can also query on the id of the country specifying the LookupId attribute in the FieldRefelement: If you want to specify two filter criteria you also have to specify a join operator And or Or. If you want to specify more filter criteria you have to nest them in a very specific way: For each extra criterion you have to add an extra join operator at the outside of the query and add the criterion at the end: If you need to retrieve items from a list when developing web parts, application pages or custom field types you can best use the SPQueryobject from the SharePoint object model. This object is located in theMicrosoft.SharePoint namespace of the Microsoft.SharePoint.dll located in the Global Assembly Cache. Instantiate the object as follows: The most important property is the Query property, which needs to be set to your CAML query: At this point you can execute the query on your list: A small remark with the GetItems method of the SPList instance: this method returns a collection of typeSPListItemCollection. It is possible that it is easier working with a DataTable. In that case you can execute the query as follows: The query will not only return all list items that have their last name set to Smith, but also all columns of each list item. In cases you work with large lists it can be important to retrieve a subset of list items containing only the columns you need. In that case you will have to set the ViewFields property of theSPQuery object. You can do this by specifying all columns you want to see returned: This will return the first name and the last name of the retrieved employees, but also the system fields like the ID and the Created date. The major disadvantage of the SPQuery object is that you can query only one list. If you want to query more than one list you will have to use the SPSiteDataQuery. More on this object in a later article because it earns special attention. It is common knowledge by now but let me remind you that it’s always a good idea to use SPQuery to retrieve a subset of list items. You can loop through the list item collection to find the list items that match your needs but this will have a serious negative impact on the performance of your work. If you are developing office clients or any other application that will not run on the server where SharePoint is installed you will need to use the SharePoint Web Services to retrieve (or update) information from SharePoint. If you want to query a list you will need to execute the GetListItems method from theLists.asmxSharePoint web service. Working with the SharePoint web services is different in Visual Studio 2005 then in Visual Studio 2008. First you have to reference the web service in your Visual Studio project. If you work with Visual Studio 2005 you have to add a Web Reference to theLists.asmx. Then you have to instantiate the web service and pass the url of the SharePoint site, plus the location and name of the SharePoint web service. The SharePoint web services are all located in the ISAPI directory of the SharePoint 12 hive but are accessible from outside via the _vti_bin directory. Initialize the Lists.asmx web service: Then you have to set the credentials. In case you don’t need to specify a username or password you can proceed as follows: If you have to specify a user name and password: If you also have to specify a domain, use the other overload: Then you are ready to call the GetListItems method, which requires the following syntax: Not all arguments are required. Arguments like view name, row limit and web ID are optional. The query node argument will contain the CAML query as explained in previous sections but need to be enclosed within a <Query> and </Query> tag: The same applies to the ViewFields node: As you will have noticed, you also need a QueryOptions node. This node will be explained later in this series. If you have no QueryOptions to specify you can pass in an empty node: But in case you are working with Visual Studio 2008 you will have to add a Service Reference to theLists.asmxweb service. If you don’t want to work asynchronously you have to uncheck the Generate Asynchronous Operations option in the Advanced dialog. When closing the dialog box Visual Studio automatically adds an app.config or web.config file. Open this file and locate the security section. The following XML is generated automatically: Replace it with the following: Return to your code and instantiate the web service: You also have to set the necessary credentials. If you can work with the default credentials you can write the following: If necessary you can also pass user name, password and domain: If necessary you can set the URL to the web service dynamically: Now you are ready to call the GetListItems method of the web service. There is no difference in calling this method from within Visual Studio 2005: The returned XML is rather complex and needs some special attention. I will come back on this in a later article. Executing a query is not only about CAML. When working with the SPQuery object you can set different properties to influence the returned list items. When working with the SharePoint web services, these options are translated into CAML and are part of the QueryOptions element. Setting this property limits the number of rows returned in the result set. When working with the SPQuery object: When working with the GetListItems method of the Lists.asmx web service: When this Boolean property is set to True, the result set will not only return the columns as defined in theViewFields property, but also the columns that you defined in the list as required. When working with the SPQuery object: When working with the GetListItems method of the Lists.asmx web service: Setting this Boolean property specifies whether the query returns dates in Coordinated Universal Time (UTC) format. The syntax is similar to that of the previously described property. When you omit this property or set it to false, user fields will return the login name of the user. Setting this Boolean property to true (or include it in the QueryOptions node), user fields are returned as follows: The returned value includes the login name, e-mail address, Session Initiation Protocol (SIP) address, and title, when present, which causes a user field to behave as a multilookup field. The syntax is similar to that of the previously described property. There are some other query options to set but they will be explained in detail in the next section. Some special types of lists require more specialized CAML queries. In some cases it only affects the CAML but in other cases you have to set extra SPQuery properties. If you execute this query against the Listsweb service it will affect another argument of the GetListItems method, i.e. the QueryOptions argument. A first variant I will explain is how you can work with folders and sub folders. A folder is a special list item on a list or document library. If you execute a standard CAML query you will end up with list items from the root folder. If you want to query all folders and sub folders of a list or document library, you have to define extra query options. If you are working with the object model you have to set the ViewAttributes property of theSPQuery object as follows: If you work with GetListItems method of the Lists.asmx SharePoint web service, you have to define an extra node with the QueryOptions element: If you want to query a specific sub folder using the SPQuery object, you have to set the Folder property: When working with the web services you have to do the following: Filtering on DateTime fields also requires some extra attention. First of all when querying for a specific date, you have to use the SharePoint datetime notation: But in this case the date is hard coded and the time part will not be taken into account. This query will return all list items with a start date as of 10 August 2008, also those starting before 10 o’clock. If you want your query to take into account the time part, you have to use a special attribute IncludeTimeValue that you can set on the FieldRef element: This query will return all list items with a start date as of 10 August 10 o’clock. As already said, this way the date is hard coded. If you want your query a bit more dynamic, you can always use the element Today. Today will not take a time part into account. Unfortunately a Now element doesn’t exist. You can also add or subtract a number of days from today’s date. In that case you have to add the Offsetattribute to the Today element. The Offset attribute accepts a positive value for adding days and a negative value for subtracting days. As all this is pure CAML, the query is the same whether you work with the object model or the SharePoint web services. Calendar lists also require a little more attention. A calendar list is based on the Event content type. Normal events can be retrieved with the usual CAML queries. It’s only when you start working with recurring events (events that occur once a day or twice a week or month) that you run into problems. The Event content type defines a field named fRecurrence, which is set to 1 when a recurring event is created. When the Calendar view is rendered a recurring event is split into a number of event instances, one for each recurrence. The definition of the recurrence is XML stored in another field defined by the Event content type, i.e.RecurrenceData. An example of such a definition looks as follows: Fortunately you don’t have to parse the XML yourself to get the actual instances of the recurring event. You can use a combination of CAML syntax and query options to achieve this. When working with the SPQuery object you need to set the ExpandRecurrences property to true. You also have to specify a date in the CalendarDate property. This date will be used to compare the recurring event instances. Next you have to add a DateRangesOverlap element to the Where clause of the CAML query. This element is used in queries to compare the dates in a recurring event with the date specified in theCalendarDateproperty to see whether they overlap. The CAML looks as follows: The <Month /> element is used to specify that all occurrences within a month need to be returned. You can also specify Day, Week or Year. In case you need to work with the GetListItems method of the Lists.asmx web service, you translate the query options into XML: Read more about Calendar lists here: http://blogs.msdn.com/sharepoint/archive/2007/05/14/understanding-the-sharepoint-calendar-and-how-to-export-it-to-ical-format.aspx This is it about retrieving List Items using CAML. Don’t hesitate to post your comments and questions below.Introduction
Building the CAML
<OrderBy>
<FieldRef Name='LastName' Ascending='False' />
</OrderBy>
<Where>
<Eq>
<FieldRef Name='LastName' />
<Value Type='Text'>Janssens</Value>
</Eq>
</Where>
Operators
Fields
Value
<Where>
<Eq>
<FieldRef Name='Country' />
<Value Type='Lookup'>Belgium</Value>
</Eq>
</Where>
<Where>
<Eq>
<FieldRef Name='Country' LookupId='True' />
<Value Type='Lookup'>15</Value>
</Eq>
</Where>
<Where>
<And>
<Eq>
<FieldRef Name='LastName' />
<Value Type='Text'>Janssens</Value>
</Eq>
<Geq>
<FieldRef Name='Age' />
<Value Type='Number'>21</Value>
</Geq>
</And>
</Where>
<Where>
<And>
<And>
<Eq>
<FieldRef Name='LastName' />
<Value Type='Text'>Janssens</Value>
</Eq>
<Geq>
<FieldRef Name='Age' />
<Value Type='Number'>21</Value>
</Geq>
</And>
<Lt>
<FieldRef Name='Age' />
<Value Type='Number'>60</Value>
</Lt>
<And>
</Where>
<Where>
<And>
<And>
<And>
<Eq>
<FieldRef Name='LastName' />
<Value Type='Text'>Janssens</Value>
</Eq>
<Geq>
<FieldRef Name='Age' />
<Value Type='Number'>21</Value>
</Geq>
</And>
<Lt>
<FieldRef Name='Age' />
<Value Type='Number'>60</Value>
</Lt>
</And>
<Eq>
<FieldRef Name='Country' />
<Value Type='Lookup'>Belgium</Value>
</Lt>
</And>
</Where>
Retrieving List Items with CAML using the SharePoint Object Model
SPQuery qry = new SPQuery();
string camlquery = "<OrderBy><FieldRef Name='Country' /></OrderBy><Where>"
+ "<Eq><FieldRef Name='LastName' /><Value Type='Text'>Smith</Value></Eq>"
+ </Where>";
qry.Query = camlquery;
SPListItemCollection listItemsCollection = list.GetItems(qry);
DataTable listItemsTable = list.GetItems(qry).GetDataTable();
qry.ViewFields = "<FieldRef Name='FirstName' /><FieldRef Name='LastName' />";
Retrieving List Items with CAML using the SharePoint Web Services
Retrieving List Items from the Lists.asmx using Visual Studio 2005
ListService listsWs = new ListService.Lists();
listsWs.Url = siteUrl + @"/_vti_bin/lists.asmx";
listsWs.Credentials = System.Net.CredentialCache.DefaultCredentials;
listsWs.Credentials = new System.Net.NetworkCredential(userName, password);
listsWs.Credentials = new System.Net.NetworkCredential(userName, password, domain);
resultNode = _sharepointSite.ListsWSS.GetListItems(listName, viewName,
queryNode, viewFieldsNode, rowLimit, queryOptionsNode, webID);
XmlDocument camlDocument = new XmlDocument();
XmlNode queryNode = camlDocument.CreateElement("Query");
queryNode.InnerXml = "<OrderBy><FieldRef Name='Country' /></OrderBy><Where>"
+ "<Eq><FieldRef Name='LastName' /><Value Type='Text'>Smith</Value></Eq>"
+ </Where>";
XmlNode viewFieldsNode = camlDocument.CreateElement("ViewFields");
viewFieldsNode.InnerXml = "<FieldRef Name='FirstName' />"
+ "<FieldRef Name='LastName' />";
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
Retrieving List Items from the Lists.asmx using Visual Studio 2008
<security mode="None">
<transport clientCredentialType="None" proxyCredentialType="None"
realm="" />
<message clientCredentialType="UserName" algorithmSuite="Default" />
</security>
<security mode="TransportCredentialOnly">
<transport clientCredentialType="Ntlm"/>
</security>
ListsWS.ListsSoapClient ws = new ListsWS.ListsSoapClient();
ws.ClientCredentials.Windows.ClientCredential =
System.Net.CredentialCache.DefaultNetworkCredentials;
ws.ClientCredentials.Windows.AllowedImpersonationLevel =
System.Security.Principal.TokenImpersonationLevel.Impersonation;
ws.ClientCredentials.Windows.ClientCredential =
new System.Net.NetworkCredential("Administrator", "secret", "U2UCOURSE");
ws.ClientCredentials.Windows.AllowedImpersonationLevel =
System.Security.Principal.TokenImpersonationLevel.Impersonation;
ws.Endpoint.Address =
new System.ServiceModel.EndpointAddress(
"http://wss.u2ucourse.com/_vti_bin/lists.asmx");
XmlDocument camlDocument = new XmlDocument();
XmlNode queryNode = camlDocument.CreateElement("Query");
queryNode.InnerXml = "<OrderBy><FieldRef Name='Country' /></OrderBy><Where>"
+ "<Eq><FieldRef Name='LastName' /><Value Type='Text'>Smith</Value></Eq>"
+ </Where>";
XmlNode viewFieldsNode = camlDocument.CreateElement("ViewFields");
viewFieldsNode.InnerXml = "<FieldRef Name='FirstName' />"
+ "<FieldRef Name='LastName' />";
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
resultNode = _sharepointSite.ListsWSS.GetListItems(listName, viewName,
queryNode, viewFieldsNode, rowLimit, queryOptionsNode, webID);
Query Options
RowLimit
qry.RowLimit = 10;
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<RowLimit>10</RowLimit>";
IncludeMandatoryColumns
qry.IncludeMandatoryColumns = true;
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
queryOptionsNode.InnerXml =
"<IncludeMandatoryColumns>True</IncludeMandatoryColumns>";
DatesInUtc
ExpandUserField
Karine Bosch,#U2UCOURSE/karine,#karine@U2UCOURSE.COM,#,#Karine Bosch
Special Types of Queries
Building queries for working with Folders
qry.ViewAttributes = "Scope='Recursive'";
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<ViewAttributes Scope=/"Recursive/" />";
qry.Folder = list.ParentWeb.GetFolder("Folders DocLib/2008");
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<Folder>Folders DocLib/2008</Folder>";
Building Queries for working with DateTime Values
<Where>
<Ge>
<FieldRef Name="StartDate" />
<Value Type="DateTime">2008-08-10T10:00:00Z</Value>
</Ge>
</Where>
<Where>
<Ge>
<FieldRef Name="StartDate" IncludeTimeValue="TRUE" />
<Value Type="DateTime">2008-08-10T10:00:00Z</Value>
</Ge>
</Where>
<Where>
<Ge>
<FieldRef Name="StartDate" />
<Value Type="DateTime"><Today /></Value>
</Ge>
</Where>
<Where>
<Ge>
<FieldRef Name="StartDate" />
<Value Type="DateTime"><Today Offset="10" /></Value>
</Ge>
</Where>
Building Queries for Calendar Lists
<recurrence>
<rule>
<firstDayOfWeek>su</firstDayOfWeek>
<repeat>
<weekly mo="TRUE" we="TRUE" weekFrequency="1" />
</repeat>
<repeatForever>FALSE</repeatForever>
</rule>
</recurrence>
<Where>
<DateRangesOverlap>
<FieldRef Name=/"EventDate/" />
<FieldRef Name=/"EndDate/" />
<FieldRef Name=/"RecurrenceID/" />
<Value Type=/"DateTime/"><Month /></Value>
</DateRangesOverlap>
</Where>
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<ExpandRecurrences>TRUE</ExpandRecurrences>"
+ "<CalendarDate>2008-08-12T10:00:00Z</CalendarDate>";
- Writing CAML Queries For Retrieving List Items from a SharePoint List1
- Writing CAML Queries from Retrieving List Items form SharePoint List
- Efficiently Delete / Purge All Items from a SharePoint List
- Rest Api how to retrieve list items from SharePoint Online
- Retrieving the list of items in a directory all at once
- 如何使用CAML 批量更新SharePoint List
- Retrieving a List of Contacts
- Retrieving a List of Contacts
- sharepoint 查询统计list数据,使用CAML联合datatable方法
- Retrieving Details for a Contact
- Retrieving Details for a Contact
- How to only display "This Week" items in a SharePoint List
- Retrieving Media from a Content Resolver
- Sharepoint 中的CAML 语言
- SHAREPOINT - CAML列表查询
- SHAREPOINT - CAML列表查询
- Adding List items into a List Folder
- U2U CAML Query Builder for SharePoint 2003 and SharePoint 2007 (Windows Version)
- 交互操作示例
- 详解Silverlight和WPF互相扩展(转载)
- JavaMail 发送邮件
- 对View DrawingCache的理解(转)
- linux下的共享库(动态库)和静态库
- Writing CAML Queries For Retrieving List Items from a SharePoint List1
- JavaMail Pop获取邮件
- Xml与DataTable相互转换方法
- VPS服务器架设:CentOS PPTPD VPN一键安装包
- SecureCRT 正常显示中文及显示颜色的配置
- JavaMail 如何只处理新邮件
- java保留两位小数
- 如何让对话框中的控件和对话框一起变小变大?
- JavaMail 解析附件