From tMSSQLInput to txmlMap with multiple loops

[Anonymous]

Hi all,

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 ?

Many thanks,

Best regards

[Anonymous]

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.

[Anonymous]

https://help.talend.com/reader/_mUSNSeiAAphzZe6zucMTQ/MDauhq9nl1m0FXZX~aHp9Q

[vapukov]

perfect:

same as for any links for this "secret product"

Look ...good, try follow there link " Talend Data Mapper is your answer.":

nothing 🙂

[Anonymous]

Hi all,

Many thanks for your answers, but it seems TDM is not included in Talend Open Studio. Maybe it's possible to get a solution by using a tJava component and try to create a xml doc. I'll try to and tell if I manage to do that way.

Best Regards

[Anonymous]

Hello,

Talend data mapper feature will be available in the studio only if you have subscribed to one of the Talend Platform products not open source.

TalendHelpCenter:Talend Data Mapper User Guide

Many thanks!

Best regards

Sabrina