topic Re: From tMSSQLInput to txmlMap with multiple loops in Talend Studio

From tMSSQLInput to txmlMap with multiple loops

Anonymous — Sat, 16 Nov 2024 09:11:04 GMT

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

Re: From tMSSQLInput to txmlMap with multiple loops

Anonymous — Thu, 12 Oct 2017 06:35:59 GMT

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.

Re: From tMSSQLInput to txmlMap with multiple loops

Anonymous — Thu, 12 Oct 2017 06:37:56 GMT

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

Re: From tMSSQLInput to txmlMap with multiple loops

vapukov — Tue, 17 Oct 2017 03:52:39 GMT

perfect:

same as for any links for this "secret product"

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

nothing 🙂

Re: From tMSSQLInput to txmlMap with multiple loops

Anonymous — Fri, 27 Oct 2017 09:30:04 GMT

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

Re: From tMSSQLInput to txmlMap with multiple loops

Anonymous — Mon, 30 Oct 2017 02:58:43 GMT

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