Knime - Generic Web Service Client - Sharepoint List - Get list items

KNIME Analytics Platform
1

Hi everybody,
has anybody successfully connected Knime to a sharepoint list using the generic web service client?
My goal would be to import all the items of a Sharepoint list, using the getlistitems method

https://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx

Unfortunately, I don't know how the parameters should be configured.

Does anybody have experience with it?

Thank you.

2

Apparently it is possible and it has been done before. This earlier post contains some details and an explanation. Have a look here.

Cheers,
Marco.

3

Hi Marco,
I appreciate your answer.
The solution you linked reflects what I am currenlty doing; unfortunately, I still can't figure out how to configure the web service node to get all the items from a Sharepoint list through the GetListItems method.

This method requires 7 parameters (listname, viewname, query, viewFields, rowLimit, queryOptions and webID).
Some of these parameters have to be simple strings, while others are supposed to be XML nodes.

This is what I realised so far:

  • The web service node has proved to work with other methods when all the parameters have a type=string.

    For instance, the node is working with the GetList method, which just requires a string parameter (the list name). The method is correctly returning the list general information and I can read these details though the xpath node. 
    Unfortunately the GetList method is just returning the list details but not the single list items (which is supposed to be the purpose of GetListItems).
  • The node does not currently work -for me- when at least one of the parameter types is not a string (in my example, the method is requesting 3 parameters to be an XmlNode object)
  • I can't figure out out to pass a "null" to optional parameters

Thank you again for your support.

 

4

Hi,

I think we are stuck here. I did some tests and GetListItems is not working for me either. I couldn't manage to properly pass the XML objects the query expects. No matter what I tried I also got back a Java error. It may be a limitation of the Generic Web Service Client node with the type of parameters it can handle.

SharePoint also has a REST api, which should be simpler to handle and less painful to use. Did you look into that possibility already?

Cheers,
Marco.

5

Thank you Marco, your solution worked!

I'm using the Get resource node to pull all the items from a Sharepoint list.
The following node configuration works fine:
url: http://site url/_api/web/lists/GetByTitle(‘Test')/items
method: GET
headers:
    accept: "application/atom+xml"

If anybody is interested, you can see details of Sharepoint rest API here:
https://msdn.microsoft.com/en-us/library/office/dn292552.aspx#RetrieveLists

Finding this solution has triggered other thoughts, like using the POST node to add an item to a Sharepoint list.
This is still something I'm unable to do: the POST request would require two headers that are not included in the node

  • content-length
  • X-RequestDigest
     

I assume this is due to the inability to customise the headers in the node; this post in the community contributions is also mentioning this problem, which appears to be unresolved
https://tech.knime.org/forum/krest-nodes/krest-no-more

Are you aware of any workaround? E.g.: using the java snippet instead?

Thank you again for your great support.

 

6

This post has been very informative, but I am still getting a 403 authorization error; how are you configuring the reaquest headers views in the GET window to pass authorization?