Extract xml field and merge outputs

Highlighted
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"
Highlighted
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 GARTNER 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

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

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