I need help creating a job taking in multiple XML and iterating the through row and adding to the output row

Six Stars

I need help creating a job taking in multiple XML and iterating the through row and adding to the output row

Hello,

 

I need to take multiple XML input (using REST) in write them to a DB table. The tricky bit is that, for each row, I will need to go get additional data to complete the row. Let me explain.

 

When do my first REST query, I will get a list of orders with  (OrderID, First and last Name, Address, Total amount). Than for each order, I have to make another REST call  using the OrderID to get the items ordered, it will contain (OrderID, Item, quantity, price, etc..). I will need to combine the order row with item ordered  row to import them in my DB table.  If the orders contain two or more type of product, than I would need an order line for each product.  Not sure how to go about doing this, Should I write multiple file and than read the file back in and how do I go through the orderID to get all the items ordered.

 

Any help would be much appreciated. 

 

Thank you


Accepted Solutions
Twelve Stars

Re: I need help creating a job taking in multiple XML and iterating the through row and adding to the output row

depending from how big response for first REST request, You could:
- receive result

- parse it with tExtractXMLFields

- store result to Hash (memory) or csv
- read from Hash/csv

- using tFlowToIterate iterate over saved result one by one for request new data
- join new data with saved using tMap

 

-----------

All Replies
Twelve Stars

Re: I need help creating a job taking in multiple XML and iterating the through row and adding to the output row

depending from how big response for first REST request, You could:
- receive result

- parse it with tExtractXMLFields

- store result to Hash (memory) or csv
- read from Hash/csv

- using tFlowToIterate iterate over saved result one by one for request new data
- join new data with saved using tMap

 

-----------
Six Stars

Re: I need help creating a job taking in multiple XML and iterating the through row and adding to the output row

Thank you very much for your answer. Now I have two questions.

 

1. When iterating to Hash/CSV, I assume the rows will be appended and the file not overwriten?

 

2. How do I use the value of the first column in the REST call I am making. See the pictures for more details.

Screenshot 2018-05-22 08.02.42.pngHere is my CSV map, what I am interested in is the OrderID field

 

 

Screenshot 2018-05-22 08.03.09.pngI am trying to iterate on OrderID and set the valuable

 

Screenshot 2018-05-22 08.04.23.pngNow I am trying to use the variable to make a REST call

Twelve Stars

Re: I need help creating a job taking in multiple XML and iterating the through row and adding to the output row

1) sure - You could append in both cases 

2) what the problem with this? it must work if all other defined correct

-----------
Six Stars

Re: I need help creating a job taking in multiple XML and iterating the through row and adding to the output row

You are right, don't know what happen but this time it worked. Thank you