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