topic Re: prepare XML with multiple loop in same level in Talend Studio

prepare XML with multiple loop in same level

vapukov — Sat, 16 Nov 2024 07:12:25 GMT

The idea for this article was described several times (for example @rhall ) but questions is still here, so I decided to prepare an example.

With standard Talend components like tXMLMap and/or tMSXMLOutput it is not possible to create XML with many loops on same level, like:

<?xml version="1.0" encoding="UTF-8"?>
<customer>
    <first_name></first_name>
    <last_name></last_name>
    <emails>
        <email> <!-- This is a Loop - multiple email for customer-->
            <email></email>
            <type></type>
        </email>
    </emails>
    <phones>
        <phone> <!-- This is a Loop - multiple phones for customer-->
            <phone></phone>
            <type></type>
        </phone>
    </phones>
    <addresses>
        <address>
            <address> <!-- This is a Loop - multiple addresses for customer -->
                <address_line_1></address_line_1>
                <address_line_2></address_line_2>
                <address_line_3></address_line_3>
                <postcode></postcode>
                <country></country>
            </address>
        </address>
    </addresses>
</customer>

Life case - client with 2 addresses, 2 phones and 2 emails and we restricted by requirements have exactly this structure.

Standard tXMLMap return wrong result, tMSXMLOutput also cannot be used in this case.

DataMapper:

documentation is empty - possible most shorterst and not useful in class
included with a most expensive license of Talend only

Solution which work in any version of Talend Studio (include Open Source):

we are split job to separate flows, 1 flow for main XML and 1 for each Loop:

in tXMLMap_1 assign PLACEHOLDER_XXXX as element value

output XML will contain:

<emails>PLACEHOLDER_EMAIL</emails>
<phones>PLACEHOLDER_PHONES</phones>
<addresses>PLACEHOLDER_ADDRESS</addresses>

we will use this at future steps for replace.

each of separated flows contain tXMLMap for generate target structure:

each output XML part must be converted from Document type to String type and cleaned from unnecessary information (tReplace_1/2/3):

Finally all flows joined by tMap (tMap_1) as string, in our case we make Join by customer_id:

in middle part of tMap we are replace each placeholder by relevant values:

row9.email==null?
row8.customer.replaceAll("<emails>PLACEHOLDER_EMAIL</emails>", "")
:row8.customer.replaceAll("<emails>PLACEHOLDER_EMAIL</emails>", row9.email)

Final XML string we can use as string to insert into database or send to API, or store to file. It is important do not convert it to Document back, because in this case Talend will add a lot of waste in document (&l & and etc)

If we need use this document as XML (Document type) in Talend on future steps - best choice store to text file and open same file but as XML.

Final proper structured XML:

<?xml version="1.0" encoding="UTF-8"?>
<customer>
    <first_name>Joe</first_name>
    <last_name>Dow</last_name>
    <emails>
        <email>
            <email>email3@test.com</email>
            <type>home</type>
        </email>
        <email>
            <email>email4@test.com</email>
            <type>work</type>
        </email>
    </emails>
    <phones>
        <phone>
            <phone>0212579971</phone>
            <type>home</type>
        </phone>
        <phone>
            <phone>0212579972</phone>
            <type>work</type>
        </phone>
    </phones>
    <addresses>
        <address>
            <address_line_1>22, Queen Street</address_line_1>
            <address_line_2>Auckland</address_line_2>
            <address_line_3>CBD</address_line_3>
            <postcode>0610</postcode>
            <country>NZ</country>
        </address>
        <address>
            <address_line_1>23, Queen Street</address_line_1>
            <address_line_2>Auckland</address_line_2>
            <postcode>0611</postcode>
            <country>NZ</country>
        </address>
    </addresses>
</customer>

Conclusion:

Solution do not cover all possible cases, but add some functionality to standard Talend Studio and ideas how it possible to resolve similar cases.

files attached:

Talend demo jobs
mysql ddl for test data

Re: prepare XML with multiple loop in same level

Anonymous — Tue, 27 Nov 2018 01:27:56 GMT

Nicely explained @vapukov

Re: prepare XML with multiple loop in same level

HanSolo44 — Wed, 22 May 2019 08:34:08 GMT

Hi Vapukov,

Thank you very much for your explanation and your example with "zip" files.
It's a very good job.

Re: prepare XML with multiple loop in same level

Rajender — Mon, 29 Jul 2019 06:18:00 GMT

@vapukov

Thank you for sharing.

I tried to import attached zip file but getting error. I am using Talend studio 7.1.1.

Attached is error(No valid items to Import) I am getting while importing zip file.

I tried to implement as per design. I have two customer , For customers XMLmap output in tlog, there is no customer id available. My understanding here is it will generate customer and corresponding xml.

Customer1| <xml>........</xml>

Please correct me if I am wrong and if possible provide valid zip file.

Best Regards

Rajender Prasad

Re: prepare XML with multiple loop in same level

vapukov — Mon, 29 Jul 2019 06:29:37 GMT

Hi,

not sure, all imported fine:

just tested from attached archive into Studio 7.1.1

Re: prepare XML with multiple loop in same level

Rajender — Mon, 29 Jul 2019 07:40:57 GMT

Thanks for quick response.

I am trying to import job in Talend 7.1.1 which is install in Windows 10. Does OS make any difference.

Re: prepare XML with multiple loop in same level

Rajender — Mon, 29 Jul 2019 09:47:36 GMT

I created similar job. While generated email XML. My understanding to generated XML for each customer.

Customer1| <emails><email> xx@xxx.com</email><email>xx2@xxx.com</email></emails>

Customer2| <emails><email> yy@xxx.com</email><email>yy2@xxx.com</email></emails>

But getting output as follows...

|<emails><email> yy@xxx.com</email><email>yy2@xxx.com</email><email> xx@xxx.com</email><email>xx2@xxx.com</email></emails>

tXMLMap has "All in one" as true as per your attached images.

Kindly suggest. If I missed anything...