Extract and aggregate data from 2 tables ( 1 to many relationship )

[_AnonymousUser]

I am relatively new to Talend and is trying to is to set up a job in TOS to solve the the following scenario:
Extract data from 2 sybase tables and sum(salary) value. Table 1 to Table 2 follows 1 to many relationship.
Table1
id, name
1 abc
2 pqr
Table2
id, salary
1 15
1 10
2 20
2 10
expected result:
1, abc, 25 ( sum (15, 10))
2, pqr 30 ( sum (20, 10 ))
How can this be modeled ? I couldn't get the tMap component to do the aggregation (sum (salary) part ). Please advise.

[Anonymous]

Thanks Shong and Cantoine for all your esponses so far. It would be really helpful if i can get your thoughts on whether i can use the AdvancedXML component in Talend 3.1.2 (in append mode) to generate the XML with multiple loop elements as seen in the structure above. If not, i believe i will have to quickly look at alternate options including writing some custom code.
Any quick suggestion on this is appreciated.

[Anonymous]

Hello
Yes, 'append the source xml file' option is available. For example:
There exists one xml file:

<rootTag>
<persons>
<person>
<id>1</id>
<name>shong</name>
</person>
<person>
<id>2</id>
<name>elise</name>
</person>
<person>
<id>3</id>
<name>haina</name>
</person>
</persons>
</rootTag>

Now, I will append another loop element to the xml file, see my screenshots.
in.csv:

1;22.23
2;32.12

Result:

<rootTag>
?
<persons>
<person>
<id>1</id>
<name>shong</name>
</person>
<person>
<id>2</id>
<name>elise</name>
</person>
<person>
<id>3</id>
<name>haina</name>
</person>
<item>
<id>1</id>
<price>22.23</price>
</item>
<item>
<id>2</id>
<price>32.12</price>
</item>
</persons>
</rootTag>

Best regards

shong

[Anonymous]

Siva et al:
This is a critically important scenario for us as well. Did you get this working? The "append" option appears to be a bit of a misnomer, in that it seem to allows you to insert new elements wherever you have a loop element defined. This is the key question: Does the append option allow arbitrary, complex elements to be added anywhere in the XML tree? Or would another approach be more robust, such as using tJavaRow, and leveraging either the DOM or, easier still, a Java XML data binding tool (Castor, JAXB, Liquid Tech's, etc.).
Opinions?

[Anonymous]

Hi,
I have written a job to fetch the data from the DB and if I use the tFileOutputXML then my o/p XML format will be
<xml>
<Person>
<id>P12</id>
<lastName>kumar</lastName>
<firstName>Arif</firstName>
<firstName>Shyam</firstName>
<deatils>
<name>hnlk1</name>
<value>1678</value>
</deatils>
<deatils>
<name>hnlk2</name>
<value>1878</value>
</deatils>
<deptId></deptId>
</Person>
</xml>

and if I use tAdvancedFileOutputXML then my Output XML format was
<xml><Department><deptId>1</deptId><deptName>ECE</deptName><deptHeadName>nikhil</deptHeadName></Department></xml>
I want to append the source XML so I am using the tAdvancedFileOutputXML, Is there any way to get the O/P XML in the 1st format by using tAdvancedFileOutputXML component?
Regards,
Pratap G.