#xmlhttp

New Post has been published on http://automated-360.com/sharepoint/using-ms-sharepoint-data-source-2/

Using MS SharePoint as data source : Part 2

(adsbygoogle = window.adsbygoogle || []).push();

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”  

(adsbygoogle = window.adsbygoogle || []).push();

<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")