XML metadata with same element

[Anonymous]

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>

<Address id="123" type="B">

<primary>true</primary>

<street>5th Street</street>

<city>New York</city>

</Address>

<Address id="543" type="S">

<primary>false</primary>

<street>8th Street</street>

<city>Kansas City</city>

</Address>

</Addresses>

Thanks!

[vapukov · Accepted solution]

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
\

[Anonymous]

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

The ConvertType is for cast the Type from Char to String

[vapukov]

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

any problem has many solutions

[Anonymous]

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

[vapukov]

not sure, You can try

[Anonymous]

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 !!

[vapukov]

just combination of LEFT and INDEX - for extract substrings