Using Powershell, CAML queries and SOAP to Read from a Sharepoint List

Similarly to my post here this post describes how we can use Powershell to extract information from a Sharepoint list.

$list = $null             
$service = $null  

# The uri refers to the path of the service description, e.g. the .asmx page            
$uri = "http://xxx/_vti_bin/Lists.asmx"

# Create the service            
$service = New-WebServiceProxy -uri $uri -Namespace SpWs -UseDefaultCredential   
$service.url = $uri

# The name of the list             
$listName = "331609D1-793D-4075-BC88-570956C6D729"             

$xmlDoc = new-object System.Xml.XmlDocument

$queryOptions = $xmlDoc.CreateElement("QueryOptions")
$queryOptionsString = "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns><DateInUtc>TRUE</DateInUtc><ViewAttributes Scope='RecursiveAll' />"
$queryOptions.set_innerXML($queryOptionsString)

$query = $xmlDoc.CreateElement("Query")
$queryString = "<OrderBy><FieldRef Name='Title' Ascending='TRUE' /></OrderBy>"
$query.set_innerXML($queryString)
$rowLimit = "999"

try{
$list = $service.GetListItems($listName, "", $query, $viewFields, $rowLimit, $queryOptions, "")
}
catch{
[System.Exception]
write-host ($_.Exception).Message
}

Once I’d received the list of values back as a System.Xml.XmlNode object, I added them to a Combobox like this:

foreach ($node in $list.data.row) {

    $customerBox.items.add($node.GetAttribute("ows_Title")) | Out-Null

}

Incidentally, I needed to retrieve some more values from my Sharepoint list for use later on. Rather than doing another Web service request, I decided to store the values I needed during the first request so I could use them later. I updated the for loop above like so:

foreach ($node in $list.data.row) {

$FTPhash.Add($node.GetAttribute("ows_Title"),(@{
        "uname" = ($node.GetAttribute("ows_FTP_x0020_Username"))
        "pword"   = ($node.GetAttribute("ows_FTP_x0020_Password"))
    }))

    $customerBox.items.add($node.GetAttribute("ows_Title")) | Out-Null

}

You can see that we’ve used a hash table to store the results. Not only is it a hash table, but it’s a hash table inside another hash table!!

A hash table is similar to the VBScript Dictionary object in that it stores key-value pairs. In our example above, the key is:

$node.GetAttribute(“ows_Title”)

and the value is another hash table of:

@{
“uname” = ($node.GetAttribute(“ows_FTP_x0020_Username”))
“pword” = ($node.GetAttribute(“ows_FTP_x0020_Password”))
}

and how do we retrieve these values?  Easy…

$ftpuname = $FTPhash.Get_Item($customerBox.SelectedItem).uname
$ftppassword = $FTPhash.Get_Item($customerBox.SelectedItem).pword

**NOTE**

I’ve just used this code in another environment and for a different client.  I was getting:

Exception calling “GetListItems” with “7” argument(s)

And the detailed exception didn’t tell me much.  It turns out it was to do with the service URL.

If i initially created the service with a URL such as https://domain.co.uk/sites/IMT/deskserv/_vti_bin/Lists.asmx and immediately afterwards I echoed out:

$service.url

It would return the URL as being: https://domain.co.uk/sites/IMT/Lists.asmx!!    (Notice the difference!) This is actually the site URL!  Hence I now explicitly set the URL of the web service proxy after we create it using:

$service.url = $uri