Home > Tutorial > SharePoint GetListItems and Complex Parameters

SharePoint GetListItems and Complex Parameters

In my previous article I showed how to connect to SharePoint web services using SOAP::Lite and how to easily pull a list of all collections on the site. Now we’ll dive a little bit deeper and look at how we can fetch items out of a list. For simple queries this is pretty easy but can become complicated if you need to include a lot of parameters. A lot of tutorials I’ve seen on this topic revert to sending raw XML as the parameter to get around the complexities of doing it in code. Personally, I think that’s a bad idea and just makes your code look horrible. I hope to demystify the problems with doing this in code for you here.

SOAP::Data

Before we get into the details of how to call GetListItems() we need to understand how SOAP::Lite deals with data parameters using the SOAP::Data class.

SOAP::Lite includes a SOAP::Data class that encapsulates the data you pass to method calls so you don’t have to manually write any XML. Personally, using SOAP::Data was a little confusing for me at first, especially when it came to complex requests with nested parameters. The example below shows the “SOAP::Data” way of passing parameters to methods.

my $som = $soap->GetListItems(
	SOAP::Data->name(listName => '{04AA9466-6118-48D4-9475-A6AF62F59C61}'),
	SOAP::Data->name(queryOptions => \SOAP::Data->value(
		SOAP::Data->name(QueryOptions => \SOAP::Data->value(
			SOAP::Data->name(IncludeMandatoryColumns => 'False'),
			SOAP::Data->name(DateInUtc => 'True')
		))
	)),
	SOAP::Data->name(rowLimit => 1000)
);

Some of you may have noticed a special syntax in the above example. If you missed it, look closely. See it yet? The calls to SOAP::Data->value() have a backslash reference operator in front of them! This is probably one of the primary idioms that break SOAP calls for beginners. Put simply, this syntax is how SOAP::Lite is able to handle nested data structures. If you need to have nested arrays or nodes and you don’t include the backslash reference in front of nested value()’s then the serialized XML that is passed to the server will be incorrect and you’ll get invalid results.

That code example is VERY verbose and annoying to read with all those SOAP::Data calls just to setup a simple method call! In my opinion, the SOAP::Lite developers should have included some shortcuts to make this a little easier on the eyes. I even read one blog recently that suggested to do the following. Note: Do not do use this example!

import SOAP::Data 'name', 'value'; # DO NOT DO THIS!!
my $som = $soap->GetListItems(
	name(listName => '{04AA9466-6118-48D4-9475-A6AF62F59C61}'),
	// ... snip ...
);

Now, importing the ‘name’ and ‘value’ subs from SOAP::Data may seem like an interesting way to make your code more compact, but the fact is, it will actually break your code because the name() and value() subs will end up ignoring the first parameter you pass to it (it tries to use it as the class prototype). So don’t do it. See my shortcuts below for a better way to do this.

SOAP::Data Shortcuts

I created two shortcut methods for name() and value() that make this a little more compact so we can setup our parameters like this:

my $som = $soap->GetListItems(
	name(listName => '{04AA9466-6118-48D4-9475-A6AF62F59C61}'),
	name(queryOptions => \value(
		name(QueryOptions => \value(
			name(IncludeMandatoryColumns => 'False'),
			name(DateInUtc => 'True')
		))
	)),
	name(rowLimit => 1000)
);

# short-cut SOAP::Data methods
sub name {
	my ($name, $value) = @_;
	my $d = SOAP::Data->name($name);
	if (ref $value eq 'HASH') {
		return $d->attr($value);
	} elsif (defined $value) {
		return $d->value($value);
	}
	return $d;
}
sub value {
	SOAP::Data->value(@_)
}

I think that’s a little easier to read and maintain. My name() sub even allows you to add attributes directly w/o having to call attr() separately. More on that later.

SharePoint GetListItems()

Let’s get down to business. You want to fetch the items from a list on your SharePoint site. To do that you use the web service method GetListItems(). This method call can take several optional parameters and one required parameter.

  • listName(required) The display name or the {GUID} for the list you want items from. (Using the {GUID} is recommended since list names can change at any time but the {GUID} will not). Note: When using the {GUID} it should be enclosed literally with curly braces “{}”.
  • rowLimit(recommended) The number of rows to return. If you don’t specify this value then the total rows returned will be dictated by the view which is not always what you want.
  • query(optional) A query that can include an OrderBy, Where and GroupBy clauses. A common use for this parameter is to specify the order in which the items will be returned.
  • viewName (optional) The {GUID} of the view to use. If not specified the default view of the list will be used. This determines the default attributes for the rowLimit, viewFields and query fields.
  • queryOptions (optional) Configure special options that affect how the items are returned. You won’t have to use this much. One of the main reasons to use this is to enable Paging support. See the MSDN documentation for more details.

Here is the full listing of the example code:

#!/usr/bin/perl
# @file ex_soap2.pl
use strict;
use warnings;
use Data::Dumper;
local $Data::Dumper::Sortkeys = 1;
local $Data::Dumper::Indent = 1;

use Authen::NTLM;
use SOAP::Lite;

my $user = '\\username'; # must have leading slash (do not include domain)
my $pass = 'password';
my $host = 'my.sharepointsite.com:443'; # the port is required for NTLM to work
my $endpoint = "https://$host/_vti_bin/Lists.asmx";

# enable NTLMv2 in Authen::NTLM
ntlmv2(1);

# initialize our SOAP connection ...
my $soap = SOAP::Lite
	->proxy($endpoint, keep_alive => 1, credentials => [$host, '', $user, $pass])
	->default_ns('http://schemas.microsoft.com/sharepoint/soap/')
	->on_action(sub { $_[0] . $_[1] })
	->readable(1)
	;

# fetch the list of items ...
my $som = $soap->GetListItems(
	name(listName => '{04AA9466-6118-48D4-9475-A6AF62F59C61}'), # required
	name(viewName => '{9A212849-8635-47CE-8F78-C38FAD16501A}'), # optional
	name(viewFields => \value( # optional
		name(ViewFields => \value(
			name('FieldRef' => {Name => 'ID'}),
			name('FieldRef' => {Name => 'Title'}),
			name('FieldRef' => {Name => 'Created'}),
			name('FieldRef' => {Name => 'Modified'}),
			name('FieldRef' => {Name => 'FileRef'})
		))
	)),
	name(query => \value( # optional
		name(Query => \value(
			name(OrderBy => \value(
				name('FieldRef' => {Name => 'Created', Ascending => 'True'}),
			))
		))
	)),
	name(rowLimit => 1000) # recommended
);
die $som->faultstring() if defined $som->fault();

# do something with the items ...
my @results = $som->dataof('//GetListItemsResult/listitems/data/row');
foreach my $data (@results) {
	my $item = $data->attr;
	#print Dumper($item);
}

# short-cut SOAP::Data methods
sub name {
	my ($name, $value) = @_;
	my $d = SOAP::Data->name($name);
	if (ref $value eq 'HASH') {
		return $d->attr($value);
	} elsif (defined $value) {
		return $d->value($value);
	}
	return $d;
}
sub value {
	SOAP::Data->value(@_)
}

Take a close look at the parameters that are passed to the $soap->GetListItems() call. Notice how the “query” parameter has a nested “Query” structure. The nested structure shown is important and its a common mistake to miss one of the nested levels, and that will break the call and cause a SOAP fault. It’s just the way Microsoft decided to make SharePoint work.

Looping over the list items

Traversing the returned items is very easy but may seem weird to those that are not familiar with how XPath query language works. XPath is a simple language that allows you to select nodes within an XML document. Once you know where a node lives within an XML response you can easily craft an XPath string to find all matching nodes and then iterate over them with a simple loop.

my @results = $som->dataof('//GetListItemsResult/listitems/data/row');
foreach my $data (@results) {
	my $item = $data->attr;
	print join(' - ', @$item{qw( ows_Created ows_Title ows_Full_x0020_Amount )}), "\n";
}

See the $som->dataof(...) in the example above. The XPath string “//GetListItemsResult/listitems/data/row” points to the list item rows we received from the XML response from the server. Each element in the array is an SOAP::Data object. Each object then has an attribute named for each column returned from the list. For example, if your list had the columns “Created”, “Title”, and “Full Amount” the XML returned from the server might look something like this (full envelope not shown here):

<z:data>
    <z:row ows_Created="2008-04-22 11:00:12" ows_Title="My Title" ows_Full_x0020_Amount="$100" />
    <z:row ows_Created="2009-04-10 03:23:20" ows_Title="More Stuff" ows_Full_x0020_Amount="$1,000" />
    <z:row ows_Created="2010-08-04 09:02:42" ows_Title="Another Row" ows_Full_x0020_Amount="$100,000" />
</z:data>

The astute reader might of noticed two things above. First, all fields are being prefixed with “ows_”. This is a SharePoint idiom that I assume helps prevent attribute names from clashing with other build in attributes in the XML. And second, the “Full Amount” column actually shows up as “ows_Full_x0020_Amount.” If a column name has any special characters like spaces or quotes they will be encoded with this special syntax “_x0000_”. And as most probably know a space encoded as a hex number is “0x20” however SharePoint pads the numbers as 16bit numbers instead of 8bit (presumably for unicode support).

Since the columns of each row are set as attributes in the XML you can get the entire record by using $data->attr() in the example above. Remember that each row returned is an SOAP::Data object and that object has an attr() method that returns all attributes on the element.

That about wraps up this tutorial. I hope you find it helpful.

Advertisements
Categories: Tutorial Tags: ,
  1. Elvis
    February 8, 2013 at 12:01 am

    Really Good, This is an excellent illustration. You are making my life easy

  2. Ashesh
    June 3, 2015 at 3:38 am

    Can you show an example on how to use the query to extract only specefic row from the List, Lets say for example where ID is less than 10

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: