topic Re: tSOAP Load SharePoint list items from xml response in Talend Studio

tSOAP Load SharePoint list items from xml response

mobmsc2 — Mon, 26 Jun 2017 14:30:09 GMT

I've managed to use tSOAP to return data stored in an on-prem sharepoint site list but I'm having trouble working with the response data.

Is there a way for me to reduce the set of SharePoint list columns that get returned
How do I extract the data out of the Response? I have been looking into tXMLMap but I'm not sure how to build the input to the XMLMap or if there is a way to have Talend do that for me

Some pointers

The output schema of tSOAP is 1 column called soap of type document, which gets detected by tXMLMap input as the attached image

If I untick "Output in Document" on tSOAP I get a schema (Header,Body,Fault) as input into tXMLPath

tSOAP Settings

Using NTML with valid domain, username and password values
ENDPoint: "https://mysharepoint.tld/SiteDirectory/MySite/_vti_bin/Lists.asmx"
SOAP Action "http:..schemas.microsoft.com/sharepoint/soap/GetListItems"
SOAP Version 1.1
Output in Document
SOAP Message: listed below as code

Soap Message

"<soap:Envelope xmlns:soap='http://www.w3.org/2003/05/soap-envelope' xmlns:soap1='http://schemas.microsoft.com/sharepoint/soap/'>
   <soap:Header/>
   <soap:Body>
      <soap1:GetListItems>
         <!--Optional:-->
         <soap1:listName>Supervisors</soap1:listName>
      </soap1:GetListItems>
   </soap:Body>
</soap:Envelope>"

Response

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<GetListItemsResponse xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<GetListItemsResult>
<listitems xmlns:s="uuid:MYUUIF"
xmlns:dt="uuid:xmluuid"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema">
<rs:data ItemCount="2">
<z:row ows_Attachments="0"
ows_ID="1"
ows_LinkTitle="Mrs"
ows_FirstName="Sally"
ows_Surname="Smith"
ows_Column4="2000;#Sally.Smith"
ows_Column5="Toyato"
ows_Modified="2014-08-21 09:30:55"
ows_FileRef="1;#SiteDirectory/MySite/Lists/Names/1_.000"
ows_MetaInfo="1;#"/>
<z:row ows_Attachments="0"
ows_ID="2"
ows_LinkTitle="Mr"
ows_FirstName="Tom"
ows_Surname="Collins"
ows_UL_x0020_Identity="2001;#Tom.Collins"
ows_Column5="Ford"
ows_Modified="2014-08-21 09:34:37"
ows_FileRef="2;#SiteDirectory/MySite/Lists/Names/2_.000"
ows_MetaInfo="2;#"/>
</rs:data>
</listitems>
</GetListItemsResult>
</GetListItemsResponse>
</soap:Body>
</soap:Envelope>

Re: tSOAP Load SharePoint list items from xml response

Anonymous — Thu, 29 Jun 2017 04:35:22 GMT

Hello
1. Copy the response and save them in a file.
2. On tXMLMap, right click on Soap node in the left table and select 'Import from file' to import the schema.
3. In the output table of tXMLMap, drag and drop the elements which you want to extract from the left table to right table.

Let me know if you have further questions.

Regards
Shong

Re: tSOAP Load SharePoint list items from xml response

mobmsc2 — Thu, 29 Jun 2017 09:27:51 GMT

Hi @shong

Thanks for helping me. I did save the tSoap output as a document and imported it into tXMLMap to get the design but when I map the output to a tLogRow I only get 1 response/row when the document contains at least 40 rows of data.

If I change tSoap to not Output in Document I get a different flow schema (Header, Body,Fault) from tSoap which I'm not sure how to work with in the XMLMap

Re: tSOAP Load SharePoint list items from xml response

Anonymous — Thu, 29 Jun 2017 10:11:47 GMT

Hi
From the example response, I see there are two rows. You need to set z:row as loop element after you import the schema from file.
If you don't check the Output in Document, it returns body with string type, you need to use tExtractXMLFields rather than tXMLMap.

Regards
Shong