I'm trying to create a REST service to retrieve information of a set of orders from a SQL Database.
I've started to use a tMsSQLInput component to create my sql statement :
SELECT MH.CustName, MH.OrderNr, MH.OrderDate, ML.Item, ML.Qty FROM MoveHead MH INNER JOIN MoveLine ML ON ML.MH_PK = MH.MH_PK
I always have only one header by order but one to n lines by order.
This give the following rows :
Cust1 | Order1 | 20170901 | Item1 | 20 Cust1 | Order1 | 20170901 | Item2 | 10 Cust1 | Order1 | 20170901 | Item3 | 30 Cust1 | Order2 | 20170910 | Item1 | 15 Cust1 | Order2 | 20170910 | Item2 | 20
I'd like to map this collection of rows via a txmlMap to match this xml flow :
<orders> <order> <header> <custName>Cust1</custName> <orderNr>Order1</orderNr> <orderDate>20170901</orderDate> </header> <line> <item>Item1</item> <qty>20</qty> </line> <line> <item>Item2</item> <qty>10</qty> </line> <line> <item>Item3</item> <qty>30</qty> </line> </order> <order> ... </order> </orders>
So I need 2 loops to do that, one for the orders and another for the lines, but it seems I'm only able to place one loop element on each level of my xml output inside the txmlMap component.
Does someone have an idea to solve this issue ?
The tXMLMap only allows to loop on one sub-element so the cleanest way is to use the Talend Data Mapper (TDM) tool that allows you to build very complex structures of many different types like XML, JSON and more. There are ways to do it without TDM but they are very simple and will fall down as the XML gets more complex. I would suggest looking at our TDM tool.