how to use tXMLMap with elements with variable number of occurrency

One Star

how to use tXMLMap with elements with variable number of occurrency

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.
Community Manager

Re: how to use tXMLMap with elements with variable number of occurrency

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: how to use tXMLMap with elements with variable number of occurrency

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
Community Manager

Re: how to use tXMLMap with elements with variable number of occurrency

Hi
The 'All in one' option does not work with "aggregate element", make sure 'all in one' is false!
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: how to use tXMLMap with elements with variable number of occurrency

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?
Community Manager

Re: how to use tXMLMap with elements with variable number of occurrency

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: how to use tXMLMap with elements with variable number of occurrency

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
Community Manager

Re: how to use tXMLMap with elements with variable number of occurrency

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: how to use tXMLMap with elements with variable number of occurrency

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>
Community Manager

Re: how to use tXMLMap with elements with variable number of occurrency

Hi
What are your expected result?
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: how to use tXMLMap with elements with variable number of occurrency

Same as input
One Star

Re: how to use tXMLMap with elements with variable number of occurrency

Hello! I was having a simliar problem and couldnt find any result, so I thought I could post my solution here.
My Problem: I had to replace certain strings as value of an element in a xml-file.
Solution: Use XSLT "transform.xsl" to first copy the whole document and in a 2. step replace the strings which are stored in the XML "replace.xml" (previously created with tAdvancedFileOutputXML)
Contents of my files:
-----------------------------
replace xml:
<?xml version="1.0" encoding="ISO-8859-15"?>
<list>
<sic sic="old-text1">new-text1</sic>
<sic sic="old-text2">new-text2</sic>
</list>
-----------------------------
transform.xsl (still needs some improvement, currently replaces whole element and not just the value of it)
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
<xslSmiley Surprisedutput omit-xml-declaration="no" indent="no" />

<!-- Copy whole document -->
<xsl:template match="node()|@*">
<xsl:copy>
<!--<xsl:apply-templates xpath-default-namespace="http://www.cdisc.org/ns/odm/v1.3" select="node()|@*"/>-->
<xsl:apply-templates select="node()|@*"/>
</xsl:copy>
</xsl:template>
<!-- Replace SIC in document with RandomID -->
<xsl:variable name="list" as="element(sic)+" select="document('replace.xml')/list/sic" />
<xsl:variable name="sic-regex" select="string-join($list/@sic, '|')" />

<xsl:template match="/ODM/ClinicalData/SubjectData/StudyEventData/FormData/ItemGroupData/ItemDataString">
<xsl:analyze-string select="." regex="{$sic-regex}">
<xsl:matching-substring>
<xsl:variable name="sic" select="." />
<ItemDataString ItemOID="FF.SUBJID">
<xsl:value-of select="$list" />
</ItemDataString>
</xsl:matching-substring>
<xsl:non-matching-substring>
<xsl:value-of select="." />
</xsl:non-matching-substring>
</xsl:analyze-string>
</xsl:template>
</xsl:stylesheet>
One Star

Re: how to use tXMLMap with elements with variable number of occurrency

I'm facing similar problem as ed.pag.
No solution still?
One Star

Re: how to use tXMLMap with elements with variable number of occurrency

Hello,
Talend Can some one explain me how i can connect to tRowGenerator to tXMLMap and i want  to show that output in the tAdvancedFileoutputXML. please reply me  ASAP.
Thank you.