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

-----------

View solution in original post

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

View solution in original post


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

-----------

View solution in original post

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

View solution in original post

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

-----------

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog