how to use tXMLMap with elements with variable number of occurrency

[Anonymous]

Hello, I am learning how to use the tXMLMap component and as an exercise I am trying to figure out a way to use tXMLMap to create a job that can read through an XML file and produce an exact replica of the same file.
The peculiar thing of this XML file is that within an element there are two compulsory elements (name and surname), an optional element (credit card number), and a list of transactions (outcomes and incomes) that can have any number of occurrencies.
The schema and a sample xml file compliant to the schema follow:
-------------------- BankAccount.xsd --------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs=" http://www.w3.org/2001/XMLSchema">
<xs:element name="collection">
<xs:complexType>
<xs:sequence>
<xs:element name="account">
<xs:complexType>
<xs:sequence>
<xs:element name="ownerSurname" type="xs:string" minOccurs="1" maxOccurs="1"/>
<xs:element name="ownerName" type="xs:string" minOccurs="1" maxOccurs="1"/>
<xs:element name="creditCard" type="xs:string" minOccurs="0" maxOccurs="1"/>
<xs:element name="outcome" type="xs:decimal" minOccurs="0" maxOccurs="unbounded"/>
<xs:element name="income" type="xs:decimal" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

-------------------------------------accounts.xml -----------------------------------------------
<collection>
<account>
<ownerSurname>Smith</ownerSurname>
<ownerName>John</ownerName>
<creditCard>xxxx-xxxx-xxxx-xxxx</creditCard>
<outcome>120.0</outcome>
<outcome>135.0</outcome>
<outcome>20.0</outcome>
<income>1120.0</income>
<income>35.0</income>
</account>
<account>
<ownerSurname>Brown</ownerSurname>
<ownerName>James</ownerName>
<outcome>1120.99</outcome>
<income>11.01</income>
<income>3.01</income>
</account>
<account>
<ownerSurname>White</ownerSurname>
<ownerName>James</ownerName>
<outcome>1</outcome>
<outcome>2</outcome>
<income>3</income>
</account>
</collection>
-------------------------------------------------------------------------------------------------
No matter the fact I read through user guide and components guide, I can't achieve this result. I think it's not rocket science and I really hope I am just missing something.
Can you provide me with a link to any example, or the actual solution?
If otherwise it's not possible at all, please tell me so.

[Anonymous]

Hi
tXMLMap allows you to create multiple loop elements, in your case, outcome and income are two loop elements, ownerSurname and ownerName are aggregate elements. see my screenshot.

Shong

[Anonymous]

Hi Shong, thanks for your reply.
As you can see from the images I attached, in my contextual menu there's not an option to "aggregate".
Please notice that I tried both by checking the "All in one" field to true and false.
Is there something I am missing??
(I am using Talend Open Studio for ESB - Version: 5.2.1 on Windows 7 Professional - SP1)
Thanks in advance,
Eduardo

[Anonymous]

Hi
The 'All in one' option does not work with "aggregate element", make sure 'all in one' is false!
Shong

[Anonymous]

Hi Shong, thanks for the reply.
I found that the only way for me to use aggregate elements is to have one only loop in the incoming flow.
This is the resulting XML:
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>Smith</ownerSurname>
<ownerName>John</ownerName>
<creditCard>xxxx-xxxx-xxxx-xxxx</creditCard>
<outcome>120.0</outcome>
<outcome>135.0</outcome>
<outcome>20.0</outcome>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>Brown</ownerSurname>
<ownerName>James</ownerName>
<creditCard/>
<outcome>1120.99</outcome>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>White</ownerSurname>
<ownerName>James</ownerName>
<creditCard/>
<outcome>1</outcome>
<outcome>2</outcome>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>Smith</ownerSurname>
<ownerName>John</ownerName>
<creditCard>xxxx-xxxx-xxxx-xxxx</creditCard>
<income>1120.0</income>
<income>35.0</income>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>Brown</ownerSurname>
<ownerName>James</ownerName>
<creditCard/>
<income>11.01</income>
<income>3.01</income>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>White</ownerSurname>
<ownerName>James</ownerName>
<creditCard/>
<income>3</income>
</account>
</collection>

Can you tell me if I am doing something wrong on the incoming bit?

[Anonymous]

Hi
Before tXMLMap, use a tSortRow to sort the incoming rows by ownerSurname and ownerName. For testing, I am able to generate a result as below:
<?xml version="1.0" encoding="ISO-8859-15"?>
<root>
<person>
<name>elise</name>
<incoming>54</incoming>
<outcoming>88</outcoming>
</person>
</root>
<?xml version="1.0" encoding="ISO-8859-15"?>
<root>
<person>
<name>sabrina</name>
<incoming>67</incoming>
<outcoming>25</outcoming>
<incoming>47</incoming>
<outcoming>41</outcoming>
</person>
</root>
<?xml version="1.0" encoding="ISO-8859-15"?>
<root>
<person>
<name>shong</name>
<incoming>74</incoming>
<outcoming>60</outcoming>
<incoming>20</incoming>
<outcoming>18</outcoming>
</person>
</root>
Shong

[Anonymous]

My job:
tFileInputXML --> tXMLMap --> tFileOutputXML
the settings of the tXMLMap are the ones I posted previously.
Now, where do I put tht tSortRow your were suggesting??
the flow that enters tXMLMap is composed of a single row: the root of the input xml file

[Anonymous]

My job:
tFileInputXML --> tXMLMap --> tFileOutputXML
the settings of the tXMLMap are the ones I posted previously.
Now, where do I put tht tSortRow your were suggesting??
the flow that enters tXMLMap is composed of a single row: the root of the input xml file

tFileInputXML -->tSortRow--> tXMLMap --> tFileOutputXML

[Anonymous]

Shong,
the output of tFileInputXML is one single row, i.e. the root node of the xml input file. Applying tSortRow the way you suggested only allows me to sort the root node, but since there is only one such node nothing really changes. In fact I get the same output as above. I will try to be a bit more detailed now, so you can compare and see where I am doing it wrong.
I will attach to this post a series of images that detail the whole job and each single component.
The input and output xml files are:
-------------------------------------Input---------------------------------
<collection>
<account>
<ownerSurname>Smith</ownerSurname>
<ownerName>John</ownerName>
<creditCard>xxxx-xxxx-xxxx-xxxx</creditCard>
<outcome>120.0</outcome>
<outcome>135.0</outcome>
<outcome>20.0</outcome>
<income>1120.0</income>
<income>35.0</income>
</account>
<account>
<ownerSurname>Brown</ownerSurname>
<ownerName>James</ownerName>
<outcome>1120.99</outcome>
<income>11.01</income>
<income>3.01</income>
</account>
<account>
<ownerSurname>White</ownerSurname>
<ownerName>James</ownerName>
<outcome>1</outcome>
<outcome>2</outcome>
<income>3</income>
</account>
</collection>

-------------------------------------Output---------------------------------
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>Smith</ownerSurname>
<ownerName>John</ownerName>
<creditCard>xxxx-xxxx-xxxx-xxxx</creditCard>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>Brown</ownerSurname>
<ownerName>James</ownerName>
<creditCard/>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>White</ownerSurname>
<ownerName>James</ownerName>
<creditCard/>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>Smith</ownerSurname>
<ownerName>John</ownerName>
<outcome>120.0</outcome>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>Brown</ownerSurname>
<ownerName>James</ownerName>
<outcome>1120.99</outcome>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>White</ownerSurname>
<ownerName>James</ownerName>
<outcome>1</outcome>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>Smith</ownerSurname>
<ownerName>John</ownerName>
<income>1120.0</income>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>Brown</ownerSurname>
<ownerName>James</ownerName>
<income>11.01</income>
</account>
</collection>
<?xml version="1.0" encoding="ISO-8859-15"?>
<collection>
<account>
<ownerSurname>White</ownerSurname>
<ownerName>James</ownerName>
<income>3</income>
</account>
</collection>

[Anonymous]

Hi
What are your expected result?
Shong