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

Posted on by
4

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
Next

Function getResults(splist, spwhere, spvalue, spreturnattribute)	

	request = "<?xml version='1.0' encoding='utf-8'?>" & _
	"<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>" & _
	"  <soap:Body>" & _
	"    <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>" & _
	"      <listName>" & splist & "</listName>" + _
	"      <query><Query xmlns=''>" & _
	"      <Where>" & _
	"      <Eq>" & _
	"      <FieldRef Name='" & spwhere & "'/>" & _
	"      <Value Type='Text'>" & spvalue & "</Value>" & _
	"      </Eq>" & _
	"      </Where>" & _
	"      </Query>" & _
	"      </query>" & _
	"    </GetListItems>" & _
	"  </soap:Body>" & _
	"</soap:Envelope>"

	'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","http://schemas.microsoft.com/sharepoint/soap/GetListItems"
		.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='http://schemas.xmlsoap.org/soap/envelope/' xmlns:namespace='http://schemas.microsoft.com/sharepoint/soap/' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'"

		xmlDoc.loadXML(.responseText)

		'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
		Next
		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

**UPDATE**

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

Posted on by
1

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