tSOAP Load SharePoint list items from xml response

[mobmsc2]

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.

1. Is there a way for me to reduce the set of SharePoint list columns that get returned
2. 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>

[Anonymous]

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

[mobmsc2]

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

[Anonymous · Accepted solution]

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