Extract xml field and merge outputs

Six Stars

Extract xml field and merge outputs

Hi All,

 

i want to get value of a xml looping field as an array. 

below the XML doc : 

<COMMAND>
    <TYPE>SUBBILRESP</TYPE>
    <TXNSTATUS>200</TXNSTATUS>
    <TXNID>XX191111.1108.A00026</TXNID>
    <DATA>
        <DETAILS>
            <COMPANYCODE>000547</COMPANYCODE>
            <ACCOUNTNO>123456789</ACCOUNTNO>
            <PREF1NAME>AccountNumber</PREF1NAME>
            <PREF1VALUE>123456789</PREF1VALUE>
        </DETAILS>
        <DETAILS>
            <COMPANYCODE>000500</COMPANYCODE>
            <ACCOUNTNO>1236789</ACCOUNTNO>
            <PREF1NAME>AccountNumber</PREF1NAME>
            <PREF1VALUE>123456789</PREF1VALUE>
        </DETAILS>
        <DETAILS>
            <COMPANYCODE>000500</COMPANYCODE>
            <ACCOUNTNO>12</ACCOUNTNO>
            <PREF1NAME>AccountNumber</PREF1NAME>
            <PREF1VALUE>123456789</PREF1VALUE>
        </DETAILS>
    </DATA>
    <MESSAGE>Transaction Details: NA wallet of provider TIGO is debited with service charge  0.00 FCFA, Commission 0.00 FCFA and  tax 0.00 FCFA.</MESSAGE>

    <TRID>778888888201911111108A0034</TRID>
</COMMAND>

i need to get the list of ACCOUNTNO

below is my extractFields component
1.PNG
 but at  the end i'm getting two rows :  

[INFO ]: mfs_new.billeraccountmgt_0_1.BillerAccountMgt - tLogRow_1 - Content of row 1: <?xml version="1.0" encoding="UTF-8"?>
<ACCOUNTNO>123</ACCOUNTNO>
<?xml version="1.0" encoding="UTF-8"?>
<ACCOUNTNO>123</ACCOUNTNO>
[INFO ]: mfs_new.billeraccountmgt_0_1.BillerAccountMgt - tLogRow_1 - Content of row 2: <?xml version="1.0" encoding="UTF-8"?>
<ACCOUNTNO>123456789</ACCOUNTNO>
<?xml version="1.0" encoding="UTF-8"?>
<ACCOUNTNO>123456789</ACCOUNTNO>

and i want to get want to build a string like this : 

"ACCOUNTNO1#ACCOUNTNO2#ACCOUNTNO3"
Community Manager

Re: Extract xml field and merge outputs

Hi
After you get the Account No, add a new column with a fixed value, and then use tDenormalize to denormalize the input rows, it will output only one string as you expected.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now

Getting Started with APIs

Find out how to get started with APIs

Read