topic Re: Merge two XML files - Same Schema - Multiple loop in Talend Studio

Merge two XML files - Same Schema - Multiple loop

RA6 — Sat, 16 Nov 2024 09:20:01 GMT

Hello all,

Can you help or advice how the following two xml files can be merge together please?

File 1:

Loop element : <addressList>

Group element : <debtor>

File 2 :

Loop element : <personalFields>

Group element : <debtor>

The output should be as follows:

Normally clientNum and debtorReferenceNum are the primary key in both files.

I cannot use "Append xml" as i am currently using Generation mode : Fast with low memory consumption

Re: Merge two XML files - Same Schema - Multiple loop

Anonymous — Wed, 06 Sep 2017 09:25:04 GMT

How big are these files likely to get? Are the files likely to be pretty consistent with what you have shown? The reason I ask is that there is a complicated way of doing this by stripping all of the data out and rebuilding from scratch (the safest way as well), or there is the quick and easy way of doing this. The quick and easy way would be to ....

1) Use a tExtractXMLField component to extract the "PersonalFields" section

2) Convert the above to a String

3) Take the other file and convert it to a String

4) Use String manipulation to insert the "PersonalFields" section inside the other XML String

5) Use a tConvertType to convert the resultant String to a Document for further processing (if necessary)

Re: Merge two XML files - Same Schema - Multiple loop

RA6 — Wed, 06 Sep 2017 11:33:20 GMT

Hello,

Thank you for your reply. I will try to explain you my idea.

Normally, i have 3 csv files.

debtors.csv - 15 Mb (contain 90,000 debtors/rows)

addresses.csv - 30 Mb (contain 265,000 addresses/rows - a debtor can have a maximum of 3 addresses based on addressType : RES, INV, BEP] )

personalfields.csv - 200 Mb (contain 6,000,000 personalfields/rows - a debtor can have a maximum of 80 personalfields based on personalfields name [see xml below])

In other words :

A debtor can have many addresses.

A debtor can have many personal fields.

In each 3 csv files, i have 2 primary keys (clientnum, debtorreferencenumber).

I want to generate an xml file with the following output :

<?xml version="1.0" encoding="UTF-8"?>
<debtors>
    <debtor>
        <clientNum>14069</clientNum>
        <debtorReferenceNumber>aa3373f5ae604ff883b3686086626d</debtorReferenceNumber>
        <activityCode>53168</activityCode>
        <debtorName>Debtor_name_5441</debtorName>
        <addressList>
            <address>
                <addressType>INV</addressType>
                <countryCode>CC86</countryCode>
                <address1>Address 1 - 6152</address1>
                <address2>Address 2 - 2252</address2>
                <address3>Address 3 - 4624</address3>
                <postalCode>8964</postalCode>
            </address>
            <address>
                <addressType>RES</addressType>
                <countryCode>CC16</countryCode>
                <address1>Address 1 - 3289</address1>
                <address2>Address 2 - 3198</address2>
                <address3>Address 3 - 7812</address3>
                <postalCode>5284</postalCode>
            </address>
        </addressList>
        <telephone>8582357</telephone>
	<mobile>5464643</mobile>
        <ACHPersonalFields>
            <ACHPersonalField>
                <clientNum>14069</clientNum>
                <debtorReferenceNumber>aa3373f5ae604ff883b3686086626d</debtorReferenceNumber>
                <personalFieldName>Location</personalFieldName>
                <personalFieldValue>value - 13098</personalFieldValue>
            </ACHPersonalField>
            <ACHPersonalField>
                <clientNum>14069</clientNum>
                <debtorReferenceNumber>aa3373f5ae604ff883b3686086626d</debtorReferenceNumber>
                <personalFieldName>Active</personalFieldName>
                <personalFieldValue>Oui</personalFieldValue>
            </ACHPersonalField>
            <ACHPersonalField>
                <clientNum>14069</clientNum>
                <debtorReferenceNumber>aa3373f5ae604ff883b3686086626d</debtorReferenceNumber>
                <personalFieldName>Annual Turnover</personalFieldName>
                <personalFieldValue>316.09</personalFieldValue>
            </ACHPersonalField>
        </ACHPersonalFields>
    </debtor>
    <debtor>
	...
	...
	...
    </debtor>
</debtors>

can you advice please?

Re: Merge two XML files - Same Schema - Multiple loop

Anonymous — Wed, 06 Sep 2017 22:07:54 GMT

You should still be able to approach this problem using the abstracted solution I suggested above. However, it may be slightly more fiddly. Essentially, you will need to extract each of the complex types as nodes with a key relating it to the debtor. Once you have extracted each of the complex types with their debtor keys, you can use String manipulation to rebuild them ensuring the xml blocks are linked to the correct debtor.

I should point out that I am suggesting this on the assumption you are using the Open Source solution. If you are using the Enterprise Edition, you might want to consider rebuilding completely using the Talend Data Mapper.

Re: Merge two XML files - Same Schema - Multiple loop

RA6 — Thu, 07 Sep 2017 14:36:34 GMT

Hello,

I am current using TOS Big Data 6.4

Also note that I am a new in using this application.

Can u explain me step by step on how should i proceed with the complex but safest way?

Can you provide an example related to my situation?

Thank you in advance.

Re: Merge two XML files - Same Schema - Multiple loop

Anonymous — Thu, 07 Sep 2017 17:32:18 GMT

It's very difficult to explain how to do this in detail I'm afraid. It would be quicker for you to post whatever data examples you have and I might be able to knock up a quick example. I'm not promising it will be the complete solution, but I should be able to provide a good basis.