Using MS SharePoint as data source : Part 2

In the last post [ Using MS SharePoint as data source : Part 1 ], we went through the understanding of SharePoint in brief. Now that you have all the information, let’s proceed on the scripting part and see how easily we can retrieve and post data to SharePoint.

This a three part series on using sharepoint as data source

Part 1 – Sharepoint and usage Basics
Part 2 – Retrieve data from Sharepoint
Part 3 – Post data to Sharepoint

We are going to use XMLHTTP object to send the request and XML DOM Document to load and parse the response. Let’s consider a SharePoint list Named as “Employee”, which contains Employee data as below. And we need to filter data based on the Grade.

Create Request

The very first thing we need to do is, to create the request with query.

Start with the fixed header

<?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/'>

Body Part – use the method GetListItems, which provides all the records in the SharePoint list, provide the list name which you need to connect.

In this case we need to mention “Employee”

<soap:Body>
    <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>
      <listName>Student</listName>"

Query Part – In order to get the required row(s) based on some condition, we need to mention the condition in the query part of the request. We explained the CAML query in the last post. In this example we would use to filter the record by Grade, let’s say all the employees with Grade as “VP”

<query><Query xmlns=''>
      <Where>
            <Eq>
                  <FieldRef Name='Grade'/>
      <Value Type='Text'>VP</Value>
            </Eq>
      </Where>
      </Query>
      </query>

The complete Request should like below

<?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>Employee</listName>
      <query><Query xmlns=''>
      <Where>
      <Eq>
      <FieldRef Name='Grade'/>
      <Value Type='Text'>VP</Value>
      </Eq>
      </Where>
      </Query>
      </query>
    </GetListItems>
  </soap:Body>
</soap:Envelope>

Scripting

Let’s proceed on to the scripting part now. As mentioned earlier we will use XMLHTTP to post the request to the service. XML DOM (Document Object Model) components include the XMLHTTP object, which provides client-side access to XML documents on remote servers through the HTTP protocol. It exposes a simple API which allows you to send requests and get the response.

Set oXML = CreateObject("Microsoft.XMLHTTP")
with oXML
  .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
End With

Here url should be your SharePoint site service url

http://yoursharepointsite/_vti_bin/lists.asmx

and the request is the one we created above

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>Emploee</listName>" + _
"      <query><Query xmlns=''>" & _
"      <Where>" & _
"      <Eq>" & _
"      <FieldRef Name='Grade'/>" & _
"      <Value Type='Text'>VP</Value>" & _
"      </Eq>" & _
"      </Where>" & _
"      </Query>" & _
"      </query>" & _
"    </GetListItems>" & _
"  </soap:Body>" & _
"</soap:Envelope>"

Once you post the request, you need to get the response out. If you need for reference you can save the response as well. To load the response xml and parse we will use DOM Document

Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
xmlDoc.async = False
xmlDoc.loadXML(oXML.responseText)
xmlDoc.save("C:\newResponse.xml")
Set elements = xmlDoc.getElementsByTagName("rs:data")
 
    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 = ".//z:row"
    Set colItem = xmlDoc.selectNodes(strQuery)

Here, You will get all your data from SharePoint in the object – colItem . Further you can loop through all the items and use as required.  In this case let’s say we need return the ‘first name’ in an array

For Each objItem in colItem
      arrNames(n) = objItem.getAttribute("ows_First_x0020_Name")
      n = n+1
next

Below is a generic function for your reference

Function GetData(spurl, splistname, spwhere, spvalue, spRequiredField)

Dim request
Dim xmlDoc
Dim elements

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>" & splistname & "</listName>" + _
"      <query><Query xmlns=''>" & _
"      <Where>" & _
"      <Eq>" & _
"      <FieldRef Name='" & spwhere & "'/>" & _
"      <Value Type='Text'>" & spvalue & "</Value>" & _
"      </Eq>" & _
"      </Where>" & _
"      </Query>" & _
"      </query>" & _
"    </GetListItems>" & _
"  </soap:Body>" & _
"</soap:Envelope>"

Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
xmlDoc.async = False

Set oXML = CreateObject("Microsoft.XMLHTTP")

  oXML.open "Get", url, False, null, null
  oXML.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
  oXML.setRequestHeader "SOAPAction","http://schemas.microsoft.com/sharepoint/soap/GetListItems"
  oXML.send request

   xmlDoc.loadXML(oXML.responseText)
   
   Set elements = xmlDoc.getElementsByTagName("rs:data") 

    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 = ".//z:row"
    Set colItem = xmlDoc.selectNodes(strQuery)
 
    Dim arrNames()
    Dim n : n=0
    For Each objItem in colItem
        ReDim Preserve arrNames(n + 1)
      arrNames(n) = objItem.getAttribute(spRequiredField)
      n= n+ 1
    Next
        
    GetData = arrNames
    Set colItem = Nothing

End Function

To use this function

url = "http://yoursite/_vti_bin/lists.asmx" 
arrName = GetData(url,"Employee","Grade","VP","ows_First_x0020_Name")

I hope you will find this post useful, in the next post we will see the easiest way of posting date or updating data to the sharepoint.