topic Re: XML metadata with same element in Talend Studio

XML metadata with same element

Anonymous — Tue, 06 Feb 2018 10:58:30 GMT

Hello Community,
I have an XML file just like this:

<?xml version="1.0" encoding="UTF-8"?>
<fields>
<field name="primary" type="B" id="123">true</field>
<field name="street" attr="B" id="123">5th Street</field>
<field name="city" attr="B" id="123">New York</field>
<field name="primary" attr="S" id="543">false</field>
<field name="street" attr="S" id="543">8th Street</field>
<field name="city" attr="S" id="543">Kansas City</field>
</fields>

I want to map it in Talend and get a CSV or XML like the following but with the component "tFileInputXML" I can only get the first row.
Is it possible to get all rows?

CSV
id;primary;street;city;type
123;true;5th Street;New York;B
543;false;8th Street;Kansas City;S

XML

<?xml version="1.0" encoding="UTF-8"?>
<Addresses>

<street>5th Street</street>

</Address>

<primary>false</primary>

<street>8th Street</street>

<city>Kansas City</city>

</Address>

</Addresses>

Thanks!

Re: XML metadata with same element

vapukov — Wed, 07 Feb 2018 01:51:29 GMT

little tricky, but this possible

1) I guess "attr" and "type" it the same, just mistyping?

so, proper XML, look like:

<?xml version="1.0" encoding="UTF-8"?>
<fields>
    <field name="primary" type="B" id="123">true</field>
    <field name="street" type="B" id="123">5th Street</field>
    <field name="city" type="B" id="123">New York</field>
    <field name="primary" type="S" id="543">false</field>
    <field name="street" type="S" id="543">8th Street</field>
    <field name="city" type="S" id="543">Kansas City</field>
</fields>

then combination of:

- proper XQuery
- tAggregate
- split columns

give expected result
\

Re: XML metadata with same element

Anonymous — Wed, 07 Feb 2018 09:48:39 GMT

Thanks @vapukov
Yesterday I created another solution that look like this:

The ConvertType is for cast the Type from Char to String

Re: XML metadata with same element

vapukov — Wed, 07 Feb 2018 09:52:38 GMT

yes, tPivot is also was variant, I just choose native as example (one of the reasons - no additional files)

any problem has many solutions

Re: XML metadata with same element

Anonymous — Wed, 07 Feb 2018 09:55:52 GMT

Yes, sure!
In my specific case an additional file is better.
Thanks a lot. Can I set 2 answer as solution?

Re: XML metadata with same element

vapukov — Wed, 07 Feb 2018 09:57:37 GMT

not sure, You can try

Re: XML metadata with same element

Anonymous — Wed, 07 Feb 2018 13:47:49 GMT

Hello vapukov,

Could you please provide me what the exact conditions have you passed in Expression Editor for the variables of v_primary,v_street,v_city ?

Thanks very much !!

Re: XML metadata with same element

vapukov — Wed, 07 Feb 2018 22:59:26 GMT

just combination of LEFT and INDEX - for extract substrings