SharePoint, CAML, and PowerShell

A while ago I wrote about getting a list of items from a SharePoint list. I had mentioned that you can do some clever things to select which list items you want without having to have all the items returned. Basically, this will allow you pass in a filter to the web service call so it will only return the items you care about.

This ability comes from a Query Language called CAML that is used pretty heavily in SharePoint. It definitely looks a little odd at first glance but essentially it is like SQL in an XML syntax. I think the easiest way to describe this is with an example.

<Query>
  <Where>
    <And>
      <Eq>
        <FieldRef Name="SomeColumnName"></FieldRef>
        <Value Type="Text">data1</Value>
      </Eq>
      <Eq>
        <FieldRef Name="aDifferentColumn"></FieldRef>
        <Value Type="Text">data2</Value>
      </Eq>
     </And> 
  </Where>
</Query>

This is just some XML. It’s saying it is a query where the column name “SomeColumnName” has the value “data1” AND the column aDifferentColumn has the value “data2”

You can use all kinds of different tags for standard operators like “equal, not equal, less than, greater than, as well as AND’s and OR’s. For more details on the CAML query schema, you can go here.

So how would we use this in PowerShell? It starts with Here Strings. Assuming we are going to use the query above, you would put it into a herestring and store it in a variable called $query as shown below. Also note that I am casting the variable to [XML]

[xml]$query = @"
<Query>
  <Where>
    <And>
      <Eq>
        <FieldRef Name="SomeColumnName"></FieldRef>
        <Value Type="Text">data1</Value>
      </Eq>
      <Eq>
        <FieldRef Name="aDifferentColumn"></FieldRef>
        <Value Type="Text">data2</Value>
      </Eq>
     </And> 
  </Where>
</Query>
"@

Now that we have this query variable, we can pass it as a parameter in the constructor for the GetListItems. Remember in the last article we passed a list name and six $null’s. One of those options is a query.

Assuming we have a webservice object for a Sharepoint list (which you can see in the previous article using the wonderful “Get-WebService” function) you can use the following code to return only the rows that meet the criteria of the query.

$result = $webService.GetListItems($listname,$null, $query, $null, $null, $null, $null)

There are some other options that we can play with as well to make  a lot of processing and filtering happen server side so that we only get what we want on the client side as well as minimize the amount of data that has to come back over the wire.

Comments (3) -

Is it just me or does everything to do with SharePoint seem overcomplicated?  Cool post, though.

Absolutely.  But once you get the hang of it its not all that bad. Most of it is XML which actually makes using PowerShell with it not too bad.

search engine 1/13/2015 12:16:00 PM

Can I just say what a relief to find someone who actually knows what theyre talking about on the internet. You definitely know how to bring an issue to light and make it important. More people need to read this and understand this side of the story. I cant believe youre not more popular because you definitely have the gift.

Comments are closed