Kae Travis

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

Posted on by in VBScript

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='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>" & _
'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'"
'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 VBScript, CAML queries and SOAP to Read from a Sharepoint List
Using VBScript, CAML queries and SOAP to Read from a Sharepoint List

4 thoughts on “Using VBScript, CAML queries and SOAP to Read from a Sharepoint List

  1. I kոow thiѕ if off topic but I’m looking into starting my own weblog and waѕ cսrious what all is required to get setup?

    I’m assuming having a blpog llike yourѕ would cost a pretty peոny?
    I’m not very web savvy so I’m not 100% positive.
    Any suggestions orr advicе would be greatly appreciated.

  2. Hi there

    You’re code works and is great! I just have one question… is it meant not to capture/pull data from columns which contains a large number of characters and lines?

  3. Hi. No, I’ve never had that issue. Are you getting a specific error message? Also, I use CAML Helper to run my CAML queries through first as a test: http://spcamlqueryhelper.codeplex.com/. Could be worthwhile doing this to see what data it returns.

Leave a Reply