Chapter 1
Collaborative Application Markup Language (CAML) Primer
IN THIS CHAPTER
The Collaborative Application Markup Language (CAML) is used in SharePoint to query lists and help with the creation and customization of sites. After you start digging deeper, programming is almost a required skill set that can help you easily get data from SharePoint. This chapter shows you how to create CAML queries to extract data from lists.
The CAML Language
The CAML language has been associated with SharePoint since the first version, SharePoint 2001, and SharePoint Team Services. It is based on a defined Extensible Markup Language (XML) document that will help you perform a data manipulation task in SharePoint. It is easy to relate a list to CAML if you compare it to a database table and query. When querying a database, you could get all of the records back from the table and then find the one that you want, or you can use a Structured Query Language (SQL) query to narrow the results to just the records in which you are interested. CAML helps you to do just this.
A CAML query must be a well-formed XML document that is composed of the following elements:
This simple CAML query definition defines a filter where a field equals a specified value using the Eq element. In addition, one or many FieldRef elements can be specified inside the OrderBy element to sort by one or many columns.
In the preceding example, a CAML query can define one or many of the comparison operators listed in Table 1.1 that will be used to further filter the data that the query returns.
Table 1.1. CAML Comparison Operators
You must supply all of these elements with a FieldRef child element. The FieldRef element specifies the SharePoint-specific name of the column that is being evaluated. In addition, almost all of the query elements (with the exception of IsNotNull and IsNull) require that you also specify a Value child element. This is where you will specify what value to evaluate the specified FieldRef element against.
Unfortunately, SharePoint doesn’t always intuitively name each of the FieldRefs that you need to reference. The following code is an example that you can use to extract the FieldRef name from a list by using a console application and the Microsoft Office SharePoint Server 2007 application programming interface (API):
Note
As you are developing in SharePoint 2007, you will soon find yourself building a set of utilities or tools that you keep handy to help you write code. The FieldRef extraction utility is an example of one of these useful tools.
The code is very simple and the output is very usable when you start writing CAML queries. Three arguments are required for the site uniform resource locator (URL), list name, and view name, respectively. Objects are created for the site, the web, and the list. Notice that an instance of SPQuery is also created. The SPQuery object is used to get the SharePoint-specific field names (or FieldRefs) from the view. Figure 1.1 illustrates the All Items view from a custom list called Employees. Note that to get the code to work correctly, you will need to recreate the Employees custom list on your site.
Figure 1.1. Employees—All Items view.
When you execute the preceding code against the All Items view of the Employees custom list, the results listed in Figure 1.2 are output to the console window.
Figure 1.2. FieldRef Extraction Utility Output.
The list’s fields are defined with FieldRef elements. For example, the three fields that are displayed in Figure 1.1, Employee Name, Salary, and Start Date, are actually represented in SharePoint as “LinkTitle”, “Salary”, and “Start_x0020_Date”, respectively.
The next section shows you how to use the FieldRefs to write a CAML query that will return results from the Employees list.
Querying a List
After you have identified the FieldRefs that you want to filter your query by, actually querying a list is quite simple. Taking into account the Employees list, you might want to create a query that displays all employees with a start date before January 1, 2003. The following is an example of a CAML query that will filter records by those with a start date beginning before January 1, 2003:
This query does two things. First, it specifies how the data is sorted when values are returned by using the OrderBy element. Notice that the results will be ordered by Title, which is actually the Employee Name field. Next, a Where element is defined that will specify the filter, which is similar in functionality to a SQL WHERE clause. The Where element defines an Lt (less than element), which contains a FieldRef element and a Value element. The FieldRef element is the column in the list and the Value element represents the data type and value that is being compared.
The following is a code excerpt that will execute the CAML query defined previously:
The code to perform the query is identical to the first code example in the chapter with the exception of setting the Query property of the SPQuery object named query. This is where you set the value of the CAML query that will be used to filter the data. The results are returned by using the GetItems method of the SPList object and an instance of the SPListItemCollection class. This collection is then iterated and the values are output to the debug window. The following is an example of the results that are returned by the CAML query:
Brandon Bobb 62000 12/1/2001 12:00:00 AM
Rob Foster 110000 1/1/1999 12:00:00 AM
Stephen Baron 79000 1/25/2002 12:00:00 AM
Though the number of records in the list is limited, the results are filtered to three records. Now what if you want to filter the list further? CAML has And and Or elements that can be used in conjunction with the Where element. The following is an example of a CAML query that filters by start dates before January 1, 2003 AND salaries lower than $80,000:
Naturally, this filters the sample list data to the following two records:
Brandon Bobb 62000 12/1/2001 12:00:00 AM
Stephen Baron 79000 1/25/2002 12:00:00 AM
As you can see, CAML queries are very easy to construct and execute. The next section highlights a free downloadable utility that you can use to easily construct and test your CAML queries.
Using the U2U CAML Query Builder
The U2U CAML Query Builder tool is a free download from U2U that you can use to create and test your CAML queries. It is a very useful tool that will save you a lot of time and effort while you are writing CAML queries. It can be downloaded from http://www.u2u.info/SharePoint/U2U%20Community%20Tools/Forms/AllItems.aspx.
Figure 1.3 illustrates the tool, which is referencing the Employees list that was discussed earlier in this chapter.
Figure 1.3. U2U CAML Query Builder.
Notice that after you select your list, the list’s columns are populated in a ListBox control. From there, you can select each column and then provide some information about how the query should be filtered. In Figure 1.1, the CAML query is filtering by all records where the Salary column is greater than $30,000.
It is also equally as easy to pretest your CAML query before you actually write any code. You can do this by clicking the Test button. Figure 1.4 illustrates the results that are returned by the CAML query that was generated.
Figure 1.4. U2U CAML Query Builder (Results).
Summary
CAML is a very powerful language that you need to add to your SharePoint skill set. This chapter provided details on how to use CAML to query lists and filter data based on specified criteria. This will help you improve performance of your Web Parts and other code that will interact with any type of list-based data.