One Star

Creating XML with repeating elements

Hi,
I am trying to create an XML output using Delimited Input data. The output XML XSD has complex element type (repeating elements) to which I am unable to assign data.
Could you please help in this regard.
Sample Input
11|Sam|1|200|2|400|3|600
Expected Output
<?xml version="1.0" encoding="utf-8"?>
<OrdDet>
<CustID>11</CustID>
<CustName>Sam</custName>
<Orders>
<SubId>1</SubId>
<Amount>200</Amount>
</Orders>
<Orders>
<SubId>2</SubId>
<Amount>400</Amount>
</Orders>
<Orders>
<SubId>3</SubId>
<Amount>600</Amount>
</Orders>
</OrdDet>

Thanks in advance.
- Shankaran
6 REPLIES
One Star

Re: Creating XML with repeating elements

Hi,
Could somebody help with this request.
Thanks in adavnce.
Regards
Shankaran
Community Manager

Re: Creating XML with repeating elements

Hello
Here is a demo,
in.csv:

11|Sam|1|200|2|400|3|600

Code on tJavaRow:
output_row.CustID = input_row.CustID;
output_row.CustName = input_row.CustName;
output_row.conn=input_row.SubId1+","+input_row.Amount1+";"+input_row.SubId2+","+input_row.Amount2+";"+input_row.SubId3+","+input_row.Amount3;

tLogRow:
Starting job ddd at 20:00 07/04/2010.
.------+--------+-----+------.
| tLogRow_1 |
|=-----+--------+-----+-----=|
|CustID|CustName|SubId|Amount|
|=-----+--------+-----+-----=|
|11 |Sam |1 |200 |
|11 |Sam |2 |400 |
|11 |Sam |3 |600 |
'------+--------+-----+------'
Job ddd ended at 20:00 07/04/2010.

out.xml:

<?xml version="1.0" encoding="ISO-8859-15"?>
<OrdDet>
<CustID>11</CustID>
<CustName>Sam</CustName>
<Orders>
<SubId>1</SubId>
<Amount>200</Amount>
</Orders>
<Orders>
<SubId>2</SubId>
<Amount>400</Amount>
</Orders>
<Orders>
<SubId>3</SubId>
<Amount>600</Amount>
</Orders>
</OrdDet>

Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Creating XML with repeating elements

Hi Shong,
Thank you so much for a detailed example. Really was helpful. However I have a query on the right approach when there are numerous complex element types (number of repeating elements is dynamic). Sample XML output is pasted below. Could you please suggest the right approach when there are numerous complex type required in XML output.
Thanks in Advance.
Regards
Shankaran
************** Sample Expected Output *****************
<?xml version="1.0" encoding="utf-8"?>
<Exec ExeI="-1" version="-1" type="SELL" capacity="P" intraFirm="false" interFirm="false" shortSell="false" legacyPlatform="BAB" accountType="NA" lang="en">
<audit:Activity creation-user="DDI" creation-date="2010-03-30T10:33:51.122-04:00"/>
<AlternateID>
<commonsSmiley Frustratedystem>AAA</commonsSmiley Frustratedystem>
<commons:Type>N/A</commons:Type>
<commons:Value>0121212 </commons:Value>
</AlternateID>
<AlternateID>
<commonsSmiley Frustratedystem>BBB</commonsSmiley Frustratedystem>
<commons:Type>CURR</commons:Type>
<commons:Value>DCM01</commons:Value>
</AlternateID>
<AlternateID>
<commonsSmiley Frustratedystem>OOD</commonsSmiley Frustratedystem>
<commons:Type>eee#</commons:Type>
<commons:Value>20100886534058</commons:Value>
</AlternateID>
<Transaction TxnI="-1" type="STND">
<AlternateId>
<commonsSmiley Frustratedystem>AAA</commonsSmiley Frustratedystem>
<commons:Type>N/A</commons:Type>
<commons:Value>0121212 </commons:Value>
</AlternateId>
<AlternateId>
<commonsSmiley Frustratedystem>BBB</commonsSmiley Frustratedystem>
<commons:Type>CURR</commons:Type>
<commons:Value>DCM01</commons:Value>
</AlternateId>
<EntryDate>2099-03-29T20:14:00.000</EntryDate>
<Status>ACTV</Status>
<LegCount>1</LegCount>
<BranchCode>Y</BranchCode>
</Transaction>
<Status>ACTV</Status>
<Source>AAA</Source>
<OrigSystem>AAA</OrigSystem>
<TradeDate>2099-03-23T00:00:00.000</TradeDate>
<EntryDate>2099-03-29T20:14:00.000</EntryDate>
<SettlementDate>2010-03-26T00:00:00.000</SettlementDate>
<ExchangeCode/>
<BranchCode>Y</BranchCode>
<DeskCode>12386</DeskCode>
<ResponsibleLegalEntity>
<commons:LeI>-1</commons:LeI>
<commons:AlternateId>
<commonsSmiley Frustratedystem>AAA</commonsSmiley Frustratedystem>
<commons:Type>ASDA</commons:Type>
<commons:Value>01</commons:Value>
</commons:AlternateId>
</ResponsibleLegalEntity>
<Account role="BOOK">
<commons:AlternateId>
<commons:Type>ACEW</commons:Type>
<commons:Value>09876</commons:Value>
</commons:AlternateId>
</Account>
<Account role="CPTY">
<commons:AlternateId>
<commons:Type>ACWE</commons:Type>
<commons:Value>654677</commons:Value>
</commons:AlternateId>
<commons:AlternateId>
<commons:Type>MNEM</commons:Type>
<commons:Value>FSCSDSELO1</commons:Value>
</commons:AlternateId>
<sdiSmiley FrustratedDI SDIOverrideInd="1">
<sdi:Line1>ABCD 1213121223567890 </sdi:Line1>
<sdi:Line2>A/C 892342 234 234 </sdi:Line2>
<sdi:Line3> </sdi:Line3>
<sdi:Line4> </sdi:Line4>
</sdiSmiley FrustratedDI>
</Account>
<Token name="AAA_SEC_TYPE" type="string">OOO</Token>
<Token name="AAA_SEC_OFF_CODE" type="string">0</Token>
<Token name="AAA_SR_TYP" type="string">12</Token>
<Token name="AAA_CUS_GEO_CLASS" type="string">00540</Token>
<Token name="AAA_CLEARANCE_LOCATION" type="string">31</Token>
<Token name="AAA_PROCESS_CODE" type="string">2A</Token>
<Token name="BUS_DTE" type="string">2099-03-30</Token>
<Token name="AAA_SIDE_CLR" type="string">0</Token>
<ExecItem primarySecondaryIndicator="false" role="S">
<Status>ACTV</Status>
<Instrument whenIssued="false">
<commons:AlternateId>
<commons:Type>PSUC</commons:Type>
<commons:Value>90D234</commons:Value>
</commons:AlternateId>
</Instrument>
<PriceQuantityAssociation type="TRD">
<Price type="UNIT">
<Value>00099.27600000</Value>
<AsEnteredValue>00099.27600000</AsEnteredValue>
</Price>
<Quantity type="UNIT">
<Value>500000</Value>
</Quantity>
</PriceQuantityAssociation>
<PriceQuantityAssociation type="FCTR">
<Price type="UNIT">
<Value>00099.27600000</Value>
<AsEnteredValue>00099.27600000</AsEnteredValue>
</Price>
<Quantity type="UNIT">
<Value>500000</Value>
</Quantity>
</PriceQuantityAssociation>
</ExecItem>
<ExecItem role="P">
<Status>ACTV</Status>
<Instrument whenIssued="false">
<commons:AlternateId>
<commons:Type>CRCY</commons:Type>
<commons:Value>USD</commons:Value>
</commons:AlternateId>
</Instrument>
<ExecItemAmount type="NETM">
<Amount>496380</Amount>
</ExecItemAmount>
<ExecItemAmount type="PRIN">
<Amount>-496380</Amount>
</ExecItemAmount>
<ExecItemAmount type="NETU">
<Amount>496380</Amount>
</ExecItemAmount>
</ExecItem>
<ExchangeRate>
<Rate multiply="1">1</Rate>
</ExchangeRate>
<Employee role="SLSP">
<commons:AlternateId>
<commons:Type>SWWEN</commons:Type>
<commons:Value>RE3N33</commons:Value>
</commons:AlternateId>
</Employee>
<AdditionalInfo>
<Line1>YLD TO MATUR 8.000</Line1>
<Line2/>
</AdditionalInfo>
</Exec>
Community Manager

Re: Creating XML with repeating elements

Hello
However I have a query on the right approach when there are numerous complex element types (number of repeating elements is dynamic).

Output them into the same xml file one by one using the feature 'append the source xml file'. About 'append the source xml file', see 5975.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Creating XML with repeating elements

Hi Shong,
Thanks a lot for your reply. I am not sure if I posted by query correctly (or prob did not understand ''append the source xml file" feature correctly).
In the sample message posted above <AlternateID>, <PriceQuantityAssociation>, <Token>, <Account> are some of the repeating elements. The values required to populate these repeating elements will come in a single record from source (ie all the required values willbe part of column in a single message).
Going by the example job given by you earlier , to concatenate and then to normalise on the concatenated column to populate the repeating elements, my query was ,
would we need to normalise data the number of times we have repeating/group elements ?
Is there a better approach / or am I missing some link here ?
Please advise.
Thanks in advance.
-Regards
Shankaran
One Star

Re: Creating XML with repeating elements

Hi,
Could you please help with this request.
Thanks in advance.
Regards
Shankaran