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.