Four Stars

From tMSSQLInput to txmlMap with multiple loops

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

 

5 REPLIES
Employee

Re: From tMSSQLInput to txmlMap with multiple loops

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.

Employee

Re: From tMSSQLInput to txmlMap with multiple loops

Ten Stars

Re: From tMSSQLInput to txmlMap with multiple loops

perfect: Smiley Happy

 

Screen Shot 2017-10-17 at 15.46.02.png

 


same as for any links for this "secret product"

 


Screen Shot 2017-10-17 at 15.46.21.png

 

 

 

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

 


Screen Shot 2017-10-17 at 15.46.43.png

 

nothing :-)

 

-----------
Four Stars

Re: From tMSSQLInput to txmlMap with multiple loops

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

Moderator

Re: From tMSSQLInput to txmlMap with multiple loops

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.