eCommerce data: tSOAP XML to Oracle database

[Kunal_Navale]

I am attempting to pull eCommerce data (ORDER) data using tSOAP component.  Data returned by the API call is SOAP XML containing ORDER HEADER and ORDER LINEs.  Here are some characteristics of the data returned:

1) Data pulled could contain 1+ ORDER HEADERS

2) There is a ONE-MANY relationship between ORDER HEADERS and ORDER LINES.  One order header could have 1+ order lines

3) The number of ORDER LINES returned is not fixed

Please refer the sample XML file for details of the data returned. 

The intent is to process the data into ORDER_HEADER and ORDER_LINES tables respectively, with ability to be able to relate header and lines of the same order.  Please refer the attached image file for details of the table structure. 

I got started with using components tSOAP, tExtractXMLField..but lost track.  Could you please review and advise how i could approach the extraction process design?  Thank you!

[Jesperrekuh]

Your Soap response you have to LOOP over ORDER LINES, your header data will be 'retained' and at every record. 

Make sure you check Document as output from tSoap and within tXMLMap, you can import from file, look at screenshot from tXMLMap component

setLoop.jpg

[Kunal_Navale]

Thank you, Dijke!  I was able to create a talend job per your guidance.  However, when i run the job, it reads 1 record, but nothing is inserted.  I have documented and attached the Talend job details for reference purposes.  Could you please check and advise?  Thanks much!

Talend_JobDesign_Run_Summary.docx

[Anonymous]

Hello kanavale,

As Dijke rightly suggested you will have to do a loop over order lines. I have used the same xml file you provided and able to extract all the Line Item information. See the attachment of the XML input component configuration and you can customize as per your requirement in selecting the information you require out of the xml. I believe the same kind of logic should work in your job design as well by changing the loop element to SKU or indexNumber.

Regards,

Praveen K Bandi

talendXML.jpg

[Kunal_Navale]

Very strange - i mapped the columns exactly as you have shown (please refer attached).  But, i still don't get any output data, through 1 record is read.  Obviously i am doing something wrong or have missed some step.  Not sure how to best figure it out?!  Any ideas?  Thank you!

transaction_tXMLMap.JPG

[Anonymous]

Hi kanavale,

Can you print the response straight without parsing and see if the response structure is same as that of the XML sent. Because everything looks good.

Regards,

Praveen K Bandi

[Kunal_Navale]

hi Praveen:

The actual XML file has lot more tags than i need.  So, to simplify my query to community members, i had deleted the unwanted tags.  However, i did not realize it would cause issues.  Please find attached the complete XML file.  I appreciate your help here.  Thank you!

Regards,

Kunal

actual_txn.xml

[Anonymous · Accepted solution]

Hello Kunal,

What I mean is give a tLogRow component from tSoap component instead of processing. Moreover the complete xml file you share works fine at my end.

      tSoap --> tLogRow 

And check if you are getting the desired input first.

Regards,

Praveen Kumar Bandi

[Kunal_Navale]

Praveen:

Thank you!  I had actually tried the option you suggested earlier: tSoap --> tLogRow, and was able to retrieve data.  However, when i used tXMLMap, the data would not pass beyond tXMLMap component in the flow. 

Strangely, this component layout worked: tSOAP >> tXMLMap >> tFileOutputXML >> tMap >> (OrderHeaders and OrderLines) !

Thank you, Praveen and Dijke, for help in the process!

Regards,

Kunal

tSOAP_2_DB_flow.JPG