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
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.