Four Stars

XML metadata with same element

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!

Tags (2)
2 ACCEPTED SOLUTIONS

Accepted Solutions
Ten Stars

Re: XML metadata with same element

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
Screen Shot 2018-02-07 at 14.45.27.pngScreen Shot 2018-02-07 at 14.45.44.pngScreen Shot 2018-02-07 at 14.46.26.pngScreen Shot 2018-02-07 at 14.46.41.png\

 


Screen Shot 2018-02-07 at 14.45.13.png

-----------
Four Stars

Re: XML metadata with same element

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

 

Flusso2.PNGFlusso3.PNGFlusso4.PNGFlusso5.PNGFlusso6.PNG

 

 

 

 

 

 

The ConvertType is for cast the Type from Char to String

7 REPLIES
Ten Stars

Re: XML metadata with same element

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
Screen Shot 2018-02-07 at 14.45.27.pngScreen Shot 2018-02-07 at 14.45.44.pngScreen Shot 2018-02-07 at 14.46.26.pngScreen Shot 2018-02-07 at 14.46.41.png\

 


Screen Shot 2018-02-07 at 14.45.13.png

-----------
Four Stars

Re: XML metadata with same element

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

 

Flusso2.PNGFlusso3.PNGFlusso4.PNGFlusso5.PNGFlusso6.PNG

 

 

 

 

 

 

The ConvertType is for cast the Type from Char to String

Ten Stars

Re: XML metadata with same element

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

 

any problem has many solutions Smiley Happy

-----------
Four Stars

Re: XML metadata with same element

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

Ten Stars

Re: XML metadata with same element

not sure, You can try

-----------
Five Stars

Re: XML metadata with same element

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

Ten Stars

Re: XML metadata with same element

just combination of LEFT and INDEX - for extract substrings

-----------