topic Re: how to use tXMLMap with elements with variable number of occurrency in Talend Studio

how to use tXMLMap with elements with variable number of occurrency

Anonymous — Mon, 18 Feb 2013 12:07:55 GMT

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.

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

Anonymous — Tue, 19 Feb 2013 05:02:14 GMT

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

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

Anonymous — Tue, 19 Feb 2013 09:38:33 GMT

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

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

Anonymous — Wed, 20 Feb 2013 03:36:12 GMT

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

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

Anonymous — Wed, 20 Feb 2013 09:34:20 GMT

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?

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

Anonymous — Thu, 21 Feb 2013 03:30:35 GMT

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

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

Anonymous — Thu, 21 Feb 2013 17:13:14 GMT

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

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

Anonymous — Fri, 22 Feb 2013 01:53:27 GMT

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

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

Anonymous — Fri, 22 Feb 2013 09:27:12 GMT

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>

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

Anonymous — Fri, 22 Feb 2013 17:37:53 GMT

Hi
What are your expected result?
Shong

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

Anonymous — Mon, 25 Feb 2013 09:17:39 GMT

Same as input

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

Anonymous — Mon, 18 Nov 2013 11:15:54 GMT

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">
<xsl utput omit-xml-declaration="no" indent="no" />


<xsl:template match="node()|@*">
<xsl:copy>

<xsl:apply-templates select="node()|@*"/>
</xsl:copy>
</xsl:template>

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

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

Anonymous — Tue, 03 Mar 2015 06:15:15 GMT

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

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

Anonymous — Fri, 17 Jun 2016 14:37:56 GMT

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.