Get-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.