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

We use a Sharepoint list to track various information. The toolsets that we create should ideally read from these lists, so that every team member shares the same information and can see real-time data. To do this, we used VBScript (we made a HTA actually…).

It uses a SOAP request to ‘ask’ for information from Sharepoint. Part of this request uses a CAML query to specify exactly what we’re after.

Option Explicit

Dim url, list, viewFields, request, xmlDoc, elements, colItem, objItem, queryNode, clientname, resultsArray, xmlArray(), strItem, spclientname

Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
xmlDoc.async = False

url = "http://xxxx/_vti_bin/Lists.asmx"

'search in client list for 'Multi Client', where LinkTitle equals 'Multi Client', and bring me back the target platform
resultsArray = getResults("331609D1-793D-4075-BC88-570956C6D729","LinkTitle","Multi Client","ows_Target_x0020_Platform")

'do what we want with the clients here
For Each strItem in resultsArray
    Wscript.Echo strItem

Function getResults(splist, spwhere, spvalue, spreturnattribute)	

	request = "<?xml version='1.0' encoding='utf-8'?>" & _
	"<soap:Envelope xmlns:xsi='' xmlns:xsd='' xmlns:soap=''>" & _
	"  <soap:Body>" & _
	"    <GetListItems xmlns=''>" & _
	"      <listName>" & splist & "</listName>" + _
	"      <query><Query xmlns=''>" & _
	"      <Where>" & _
	"      <Eq>" & _
	"      <FieldRef Name='" & spwhere & "'/>" & _
	"      <Value Type='Text'>" & spvalue & "</Value>" & _
	"      </Eq>" & _
	"      </Where>" & _
	"      </Query>" & _
	"      </query>" & _
	"    </GetListItems>" & _
	"  </soap:Body>" & _

	'post it up and look at the response
	with CreateObject("Microsoft.XMLHTTP")
		.open "Get", url, False, null, null
		.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
		.setRequestHeader "SOAPAction",""
		.send request

		xmlDoc.setProperty "SelectionLanguage", "XPath"
		xmlDoc.async = false
		xmlDoc.validateOnParse = false
		xmlDoc.resolveExternals = false
		xmlDoc.setProperty "SelectionNamespaces", "xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' xmlns:soap='' xmlns:namespace='' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'"


		'dim strQuery : strQuery = "//soap:Envelope/soap:Body/namespace:GetListItemsResponse/namespace:GetListItemsResult/namespace:listitems/rs:data/z:row"
		dim strQuery : strQuery = ".//z:row"

		Set colItem = xmlDoc.selectNodes(strQuery)

		Dim objItemCount : objItemCount = 0
		'clear array
		ReDim xmlArray(objItemCount)

		'wscript.echo "No of Items: " & colItem.Length
		For Each objItem in colItem		
			ReDim Preserve xmlArray(objItemCount)
			xmlArray(objItemCount) = objItem.getAttribute(spreturnattribute)			
			objItemCount = objItemCount + 1
		Set colItem = Nothing

		'we could use this if we didnt filter it during the request	
		'Set queryNode = xmlDoc.selectSingleNode(".//z:row[@ows_LinkTitle = '" & clientname & "']")
		'wscript.echo queryNode.getAttribute("ows_Target_x0020_Platform")
		'Set queryNode = Nothing

		Set xmlDoc = Nothing

		getResults = xmlArray	

	end with

End Function


The example above pulls back ALL fields from the list.  We may want to refine this to only pull back specific fields by adding the following to our SOAP request:

    " 	   <viewFields><ViewFields><FieldRef Name='COMPANYNAME_x0020_Property' /><FieldRef Name='USERNAME_x0020_Property' /></ViewFields></viewFields>" + _
    "    <queryOptions>" + _
    " 	       <QueryOptions>" + _
    " 	           <IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" + _
    " 	           <ViewFieldsOnly>TRUE</ViewFieldsOnly>" + _
    " 	       </QueryOptions>" + _
    " 	   </queryOptions>" + _

The first viewFields tag above should be at the same level as the <query> tag in the example above.  In other words, you can paste this code directly above the <query> line in the SOAP request.

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' />"

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

$list = $service.GetListItems($listName, "", $query, $viewFields, $rowLimit, $queryOptions, "")
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 $ {

    $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 $ {

        "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:


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



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 and immediately afterwards I echoed out:


It would return the URL as being:!!    (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