Geeks With Blogs

News





The SharePoint Hillbilly Fewer Big Words... More Pretty Pictures...

Yes.. there’s lots of CAML blogs out there… lots of tutorials… lots of references… So, why add to the noise??  Well… two good reasons:

  1. I need to blog about something
  2. My session at SPTechCon was on SharePoint Web Services which rely heavily on CAML in SharePoint 2007 (less so in 2010 thanks to LINQ to SharePoint, but that’s another blog).

So, it’s really important that you understand CAML or at least have a reference you can go to quickly.. so Pay Attention!!!

What Exactly Is CAML?

If you go to the MSDN site: “Collaborative Application Markup Language (CAML) is an XML-based language that is used in Windows SharePoint Services to define the fields and views used sites and lists. CAML is also used to define tables in the Windows SharePoint Services database during site provisioning.”

So, CAML is a special XML based query language used to well… query data from SharePoint.

I describe it as a combination of XML, SQL, and WTF… At first glance it appears to be overly complicated and cumbersome.  After spending time with it however you can get a sense for why it is structured the way it is..and don’t tell anyone I said this… but it actually makes sense!

So… CAML is just a Query language… If you are using the SP Object Model you can use the SPQuery Class. If you are using SharePoint Web Services outside of SharePoint you will just create a string to pass to your Web Service call. Regardless… CAML is your friend… love it… like a woman that you can’t understand and frustrates you… just accept her and love her… because like SharePoint, CAML is a woman. :)

Is It That Cumbersome? Let Me See Some Examples??

Wow, kind of pushy aren’t you? Okay… so here are some CAML examples…

For the most part you can take a “Where” clause from SQL and convert it to CAML:

Operation SQL Example Converted to CAML
Equals

WHERE City = “Harrison"

<Where>
    <Eq>
      <FieldRef Name="City" />
      <Value Type="Text">Harrison</Value>
    </Eq>
</Where>

Not Equals

WHERE City <> "Harrison"

<Where>
    <Neq>
      <FieldRef Name="City" />
      <Value Type="Text">Harrison</Value>
    </Neq>
</Where>

Not Null

WHERE City <> null

<Where>
    <IsNotNull>
      <FieldRef Name=“City" />
    </IsNotNull>
</Where>

And

WHERE City = “Harrison” and State = “AR”

<Where>
    <And>
      <Eq>
        <FieldRef Name="City" />
        <Value Type="Text">Harrison</Value>
      </Eq>
      <Eq>
        <FieldRef Name="State" />
        <Value Type="Text">AR</Value>
      </Eq>
    </And>
</Where>

Or

WHERE City = “Harrison” or City = “Harrisburg”

<Where>
    <Or>
      <Eq>
        <FieldRef Name="City" />
        <Value Type="Text">Harrison</Value>
      </Eq>
      <Eq>
        <FieldRef Name="City" />
        <Value Type="Text">Harrisburg</Value>
      </Eq>
    </Or>
</Where>

Like

WHERE City LIKE “Harris”

<Where>
    <Contains>
       <FieldRef Name="City" />
        <Value Type="Text">Harris</Value>
     </Contains>
</Where>

(OR)And

WHERE (City="Harrison" OR City="Harrisburg") AND State<>"PA"

<Where>
    <And>
      <Or>
        <Eq>
          <FieldRef Name="City" />
          <Value Type="Text">Harrison</Value>
        </Eq>
        <Eq>
          <FieldRef Name="City" />
          <Value Type="Text">Harrisburg</Value>
        </Eq>
      </Or>
      <Neq>
        <FieldRef Name="State" />
        <Value Type="Text">PA</Value>
      </Neq>
    </And>
</Where>

Order By

WHERE City="Harrison" or City="Harrisburg" ORDERBY City

<Where>
    <Or>
      <Eq>
        <FieldRef Name="City" />
        <Value Type="Text">Harrison</Value>
      </Eq>
      <Eq>
        <FieldRef Name="City" />
        <Value Type="Text">Harrisburg</Value>
      </Eq>
    </Or>
</Where>
<OrderBy>
    <FieldRef Name="City" />
</OrderBy>

You get the idea. It’s not really THAT complicated, but if you are not comfortable with XML you will be MISERABLE!

Are There Any Other Operators?

I’m SO glad you asked… why… yes.. there are more operators! I obtained the following list from an article on A2Z.net called “CAML Query syntax and options in SharePoint”.  This is actually a really great article and you should probably go read it after you are done here. 

CAML Operator

Meaning

Eq

=

Gt

>

Lt

<

Geq

>=

Leq

<=

Neq

<>

Contains

Like

IsNull

Null

IsNotNull

NotNull

BeginsWith

Text begins with

OrderBy

Sort order for a query.

GroupBy

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

 

There Has Got To Be A Better Way To Generate The CAML For Me!

Wow… whine much? Yes, there are several tools out there that will generate CAML for you. Some you have to install on SharePoint and can actually work directly with a specific list. I’ve always been more of a do it yourselfer/notepad-works-for-everything type of guy. I found a great simple web site that will actually generate your CAML for you.  So, you can type in “Where City=’Harrison’” and it will automatically generate the CAML for you. Pretty cool?

Here’s the link with a couple of screen shots so you can see what I’m talking about:

1. Type in your “where” clause and click the “Sql to CAML” button

    image
    2. Copy and paste the resulting CAML where ever you need it!
image

Definitely a handy tool. I’ve used it many times. The only flaw is that if there is an error in your Sql it will not tell you what the error was.

Well.. that’s it for my Intro To CAML… Hope you learned something… Thanks again for stopping and thanks to all those who came to my session at SPTechCon!

Posted on Tuesday, February 16, 2010 12:29 PM | Back to top


Comments on this post: Another Inro to CAML Blog

# re: Another Inro to CAML Blog
Requesting Gravatar...
Nice Post. I'm trying to learn CAML. And this is one of the best "intros" I've seen so far. I especially like the translator tool.

Keep up the good work.

- Bend
Left by Ben Bahn on Feb 24, 2010 10:07 AM

# re: Another Inro to CAML Blog
Requesting Gravatar...
Yes - but what is missing?

By simply pasting the above code - does a nicely formatted grid suddenly display on the page?

Do you store the values returned?

Exactly what do you do next with the code above?
Left by WT on Mar 11, 2010 2:49 PM

# re: Another Inro to CAML Blog
Requesting Gravatar...
A faster way to build CAML Query.

http://www.u2u.net/res/Tools/CamlQueryBuilder.aspx
Left by Marcelino on Apr 12, 2010 3:08 PM

# re: Another Inro to CAML Blog
Requesting Gravatar...
Hey Mark! What would <Aggregations Value="Off" /> do within CAML?
Left by Matt Bramer on Feb 25, 2011 2:07 PM

# re: Another Inro to CAML Blog
Requesting Gravatar...
The URL for Online converter is not valid. Can you please provide a working URL?
Left by uzn on Jul 26, 2011 3:39 PM

# Provided Link is not working
Requesting Gravatar...
The link that you have provided is not working..

http://www.spsprofessional.com/sqlcaml.aspx


Regards
Vishal Wattamwar
Left by vishal wattamwar on Oct 11, 2011 4:32 AM

Your comment:
 (will show your gravatar)


Copyright © Mark Rackley | Powered by: GeeksWithBlogs.net