topic Re: Mappping CSV DATA to XML in Talend Studio

Mappping CSV DATA to XML

Anonymous — Sat, 16 Nov 2024 11:03:44 GMT

Hi,
I am mapping CSV data to XML using txmlmap, PFB the components used.
TFileInputDelimeted-->TXMLMAP--> TXMLOutput
I have input csv data like.
customer_id,name,city,zip,line1,line2,telephone
100,xyx,TA,1887,kuyt,juy, 123456
100,xyx,TA,1887,kuyt,juy, 123457

Now 2 rows having same customer but 1 address and 2 phone numbers.
In TXMLMAP I have set address element and telephone element as loop element and customer_id as aggregator.
I got out put like.
<customers>
<customer><id>100</id></customer>
<address>
<city>TA</city>
<zip>1887</zip>
<line1>kuyt</line>
<line2>juy</line>
</address>
<address>
<city>TA</city>
<zip>1887</zip>
<line1>kuyt</line>
<line2>juy</line>
</address>
<telephone>123456</telephone>
<telephone>123457</telephone>
Question is why address is repeated 2 times as I have same address but only telephone number change.
And how to resolve it?
Thanks
asing106

Re: Mappping CSV DATA to XML

Anonymous — Mon, 07 Sep 2015 07:50:21 GMT

Hi

See the configuration as shown in my screenshot, you are able the generate the result as below:
<root>
<customers>
<customer>
<id>100</id>
<address>
<city>TA</city>
<zip>1887</zip>
<line1>kuyt</line1>
<line2>juy</line2>
</address>
<telephone>123456</telephone>
<telephone>123457</telephone>
</customer>
</customers>
</root>
Best regards
Shong

Re: Mappping CSV DATA to XML

Anonymous — Mon, 07 Sep 2015 09:15:55 GMT

Hi,
Many Thanks for reply!..
But I have a doubt ,what if I have multiple address and multiple phone numbers then how the mapping will look like? We can't set two loop element under same group element.
e.g.
customer_id,name,city,zip,line1,line2,telephone
100,xyx,TA,1887,kuyt,juy, 123456
100,xyx,CA,1886,Kuyt2,juy2,123456

100,xyx,TA,1887,kuyt,juy, 123457
100,xyx,CA,1886,Kuyt2,juy2,123457
so one customer having 2 address and 2 phone number and my csv data is multi schema data.
final xml will look like..
<customer>
<address>1</address>
<address>2</address>
<telephone>1</telephone>
<telephone>2</telephone>
</customer>
PFB the Screen shot of my actual mapping.

Re: Mappping CSV DATA to XML

Anonymous — Mon, 07 Sep 2015 10:03:08 GMT

As you did, set both address and telephone as loop element, and set 'All in one' as true the settings panel.
Best regards
Shong

Re: Mappping CSV DATA to XML

Anonymous — Mon, 07 Sep 2015 10:47:17 GMT

Hi,
Not working for me.
PFB the mapping screen shot.


what it is doing including all address and telephone in to one document.
PFB some line of generated snippet.
<?xml version="1.0" encoding="UTF-8"?>
<stg:individuals >
<stg:individual>
    <opt:id>
      <opt:id>8742970647</opt:id>
    </opt:id>
    <opt:name>
      <opt:firstName>Jose</opt:firstName>
      <opt:lastName>Fuller</opt:lastName>
    </opt:name>
    <stg:address>
      <opt ostalAddress>
        <opt:line1>9</opt:line1>
        <opt:line2>Pine View</opt:line2>
        <opt:city>San Diego</opt:city>
        <opt:countyCode>
          <opt:code>US</opt:code>
        </opt:countyCode>
        <opt:state>
          <opt:code>92121</opt:code>
        </opt:state>
      </opt ostalAddress>
    </stg:address>
    <stg:telephone>
      <opt:telephoneNumber>(145)781-3877</opt:telephoneNumber>
    </stg:telephone>
    <stg:address>
      <opt ostalAddress>
        <opt:line1>9</opt:line1>
        <opt:line2>Pine View</opt:line2>
        <opt:city>San Diego</opt:city>
        <opt:countyCode>
          <opt:code>US</opt:code>
        </opt:countyCode>
        <opt:state>
          <opt:code>92121</opt:code>
        </opt:state>
      </opt ostalAddress>
    </stg:address>
    <stg:telephone>
      <opt:telephoneNumber>(145)781-3878</opt:telephoneNumber>
    </stg:telephone>
    <stg:address>
      <opt ostalAddress>
        <opt:line1>567</opt:line1>
        <opt:line2>Kim</opt:line2>
        <opt:city>Houston</opt:city>
        <opt:countyCode>
          <opt:code>US</opt:code>
        </opt:countyCode>
        <opt:state>
          <opt:code>77060</opt:code>
        </opt:state>
      </opt ostalAddress>
    </stg:address>
    <stg:telephone>
      <opt:telephoneNumber>(647)511-2481</opt:telephoneNumber>
    </stg:telephone>
    <stg:address>
      <opt ostalAddress>
        <opt:line1>567</opt:line1>
        <opt:line2>Bay</opt:line2>
        <opt:city>Alexandria</opt:city>
        <opt:countyCode>
          <opt:code>US</opt:code>
        </opt:countyCode>
        <opt:state>
          <opt:code>77061</opt:code>
        </opt:state>
      </opt ostalAddress>
    </stg:address>
    <stg:telephone>
      <opt:telephoneNumber>(647)511-2481</opt:telephoneNumber>
    </stg:telephone>
.....so on....
</individual>
</individuals>
but I want like.
<individuals>
<individual>
<address>
</address>
<address>
</address>
<telephone>
</telephone>
<telephone>
</telephone>
so on...
</individual>
<individual>
</address>
<address>
</address>
<telephone>
</telephone>
<telephone>
</telephone>
</individual>
so on..
</individuals>

Re: Mappping CSV DATA to XML

Anonymous — Wed, 09 Sep 2015 09:27:35 GMT

y, i have the same problem, how could we set multi loop element for one group? for ex: customers have x numbers of addresse and y numbers of telephone.
should i use 2 component txmlmap to group customer then loop adresse and group customer then loop telephone? at last we use look up txmlmap to join the two schema?
how could i design the job?
waiting for your replay.

Re: Mappping CSV DATA to XML

Anonymous — Wed, 09 Sep 2015 10:41:34 GMT

Hi asing106
tXMLMap doesn't support group element if there are multiple loop elements. Take a look at the 'Append the source XML file' option on tAdvancedFileOutputXML component.
Best regards
Shong