There is not yet much information on the net on how to combine Powershell with the SharePoint web services. One of the most used methods of the Lists.asmx, which is a standard SharePoint web service, is the GetListItems method. With this method you can retrieve all list items but also a subset of data when you use a CAML query. You can also limit the number of columns returned if you use a ViewFields node.

If you read previous posts and saved the necessary environment variable declarations in your profile, the only thing you should do to start is:

$listservice = New-Object Lists

$listservice.Credentials=[System.Net.CredentialCache]::DefaultCredentials

Otherwise you have to go back to my post and execute the first steps to declare the environment variables and compile and load the lists dll.

In its most simple form, you can retrieve all list items of a certain list:

$result = $listservice.GetListItems("Customers", $null, $null, $null, $null, $null, $null)

$result is an xml node containing the following:

It's the data element that contains the list items.

If you execute something like the following, you will get the last name of each customer in the result set:

$result.data.row | foreach ($_.ows_Title)

When working with the Lists.asmx SharePoint web service, all fields are prefixed with ows_.

If you want to retrieve a subset of list items you have to pass a CAML query to the GetListItems method. For example, if you want to retrieve all customers living in the city Bendigo, you have to create following CAML query:

<Query><Where><Eq><FieldRef Name='WorkCity' />

<Value Type='Text'>Bendigo</Value></Eq></Where></Query>

You execute the GetListItems method as follows:

$query = "<Query><Where><Eq><FieldRef Name='WorkCity' /><Value Type='Text'>Bendigo</Value></Eq></Where></Query>"

$result = $listservice.getlistitems("Customers", $null, $query, $null, $null, $null, $null)

You can always build your more complex queries with the U2U Caml Query Builder which exists in windows version (download) and feature version (download). Consult my blog or the U2U web site for more detailed information about building CAML queries with the tools. 

Another way to limit the number of rows returned is specifying a row limit, with or without a query:

$result = $listservice.getlistitems("Customers", $null, $query, $null, 3, $null, $null)

If you want to view the complete content of each row, you can execute the following:

You can also limit the number of columns returned in the result set by specifying a ViewFields node. Lets only retrieve the Title and Email for customers living in Bendigo:

[xml]$viewfields = "<ViewFields><FieldRef  Name='Title' /><FieldRef Name='EMail' /></ViewFields>

$result = $listservice.getlistitems("Customers", $null, $query, $viewfields, $null, $null, $null)

As you view the result you will notice that indeed the Title and the Email field are returned but also another bunch of fields. These are the system fields and will always be returned, even if you use the IncludeMandatoryColumns element of the QueryOptions (because this only works for custom fields defined as required). For completeness, you define QueryOptions as follows:

[xml]$queryoptions = "<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>"

$result = $listservice.getlistitems("Customers", $null, $query, $viewfields, $null, $queryoptions, $null)

You can read more about the GetListItems method on the msdn site.

If you need extra information about this method or about other method on other SharePoint web services, you can leave me a comment.

Nick, now you owe me a beer!