XML metadata with same element

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


Accepted Solutions
Forteen 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


All Replies
Forteen 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

Forteen 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?

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

Forteen Stars

Re: XML metadata with same element

just combination of LEFT and INDEX - for extract substrings

-----------

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog