SSIS and SharePoint Web Service – GetListItems

Recently working on SSIS to get SharePoint List Item. Using SSIS Script Component, we can call the SharePoint web service. Proxy class can be generated using Web Services Description Language Tool (Wsdl.exe)

Dim ListsService As Lists = New Lists() 'Web Service proxy class
ListsService.Url = "http://kelvinsharepointsite/_vti_bin/lists.asmx"
ListsService.Credentials = System.Net.CredentialCache.DefaultCredentials

'Create Query CAML
Dim xmlQueryDoc As New XmlDocument
Dim ndQuery As XmlNode = xmlQueryDoc.CreateNode(XmlNodeType.Element, "Query", "") 'Create <Query> node
Dim ndViewFields As XmlNode = xmlQueryDoc.CreateNode(XmlNodeType.Element, "ViewFields", "") 'Create <ViewFields> node
Dim ndQueryOptions As XmlNode = xmlQueryDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "") 'Create <QueryOptions> node

ndQuery.InnerXml = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>My Title Here</Value></Eq></Where>"
ndViewFields.InnerXml = "<FieldRef Name='Description'/>"
ndQueryOptions.InnerXml = ""
Dim listName As String = "My List Name Here"
Dim rowLimit As String = "1"

'Lists.GetListItems (listName, viewName, query, viewFields, rowLimit, queryOptions, webID)
Dim ndListItems As XmlNode = ListsService.GetListItems(listName, Nothing, ndQuery, ndViewFields, rowLimit, ndQueryOptions, Nothing)

Dim xmlResultDoc As New XmlDocument()
xmlResultDoc.LoadXml(ndListItems.OuterXml)
'For debug
'xmlResultDoc.Save("E:\Kelvin\debug.xml")

Dim nsmgr As New XmlNamespaceManager(xmlResultDoc.NameTable)
nsmgr.AddNamespace("z", "#RowsetSchema")

Dim rows As XmlNodeList = ndListItems.SelectNodes("//z:row", nsmgr)
Dim value As String = rows(0).Attributes("ows_Description").Value
MsgBox("Returned value: " + value)

'For iteration multiple rows
'For Each xNode As XmlNode In rows
'    MsgBox(xNode.Attributes("ows_Description").Value)
'Next

Leave a Reply

Your email address will not be published. Required fields are marked *