topic Re: Read XML file with attributes in Talend Studio

Read XML file with attributes

Anonymous — Fri, 02 Jun 2017 16:03:11 GMT

Hi,

i have to map a xml-file, my problem is that this file contain attributes. Thie file look like this:

How can i extract the values for ET-NUMMER, LIEFERANTENARTIKEL,EAN ?

Second problem in the second line in the XML make reference to the .dtd file:

<!DOCTYPE BDD SYSTEM "ABB BDD.dtd">

But when i leave this line in i get no rows from talend.

Thanks for help

Re: Read XML file with attributes

Anonymous — Sat, 03 Jun 2017 03:22:48 GMT

@drentsch, you can try this to extract the values,

Output:

.-------+------------------+--------------.
| tLogRow_1 |
|=------+------------------+-------------=|
|LINE_NO|DOMAIN |VALUE |
|=------+------------------+-------------=|
|40.0 |ET-NUMMER |961860800 |
|40.0 |LIEFERANTENARTIKEL |4102-230V.O.61|
|40.0 |EAN |7611386037073 |
'-------+------------------+--------------'

Re: Read XML file with attributes

vapukov — Sat, 03 Jun 2017 05:05:20 GMT

just alternative way:

Re: Read XML file with attributes

Anonymous — Sat, 03 Jun 2017 07:36:09 GMT

Thanks, this is working fine

Re: Read XML file with attributes

Anonymous — Sat, 03 Jun 2017 07:44:52 GMT

Glad to hear that @drentsch

Re: Read XML file with attributes

philippert — Thu, 28 Mar 2019 11:04:46 GMT

If you can't loop on the object, this syntaxe works too : IdentNo/@DOMAIN.

Thanks.

Re: Read XML file with attributes

sushantk19 — Wed, 25 Mar 2020 10:56:49 GMT

Hi All,

I have the below scenario:

output from my REST API is as below

|<?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmesender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmesender>
<Cube>
<Cube time='2020-03-24'>
<Cube currency='USD' rate='1.0843'/>
<Cube currency='JPY' rate='119.99'/>

<Cube currency='THB' rate='35.533'/>
<Cube currency='ZAR' rate='19.0754'/>
</Cube>
</Cube>
</gesmes:Envelope>|null |

I want to extract the values from two fields i.e currency and rate in two seperate fields in a .csv file.

currency rate

USD 1.0843

JPY 119.99

Job design and configuration is attached which is not working as of now. I am very new to talend. can you please help?

Currency_extract2.png

Re: Read XML file with attributes

vapukov — Wed, 25 Mar 2020 11:15:29 GMT

Hi,

try to use:

Loop XPath "gesmes:Envelope/Cube/Cube/Cube"

and XPath query:

"@currency"

"@rate"

regards

Re: Read XML file with attributes

sushantk19 — Wed, 25 Mar 2020 11:21:07 GMT

Unfortunately, no luck. same result.

-------+----.
| tLogRow_2 |
|=-------+---=|
|currency|rate|
|=-------+---=|
'--------+----'

[statistics] disconnected

Re: Read XML file with attributes

vapukov — Wed, 25 Mar 2020 11:23:39 GMT

XML Field could be "string" (not the expected Body 🙂 )

Re: Read XML file with attributes

sushantk19 — Wed, 25 Mar 2020 11:29:49 GMT

I didnt understand completely. Are you saying XML Field value should not be "Body"?? I just have Error_code as another option in drop down; Also the ouput generated in .csv file is coming as NULL with just two headers.

Re: Read XML file with attributes

philippert — Wed, 25 Mar 2020 11:37:13 GMT

Doesn't it work with :
Loop : /gesmes/Cube/Cube
Mapping :
- currency : @currency
- rate : @rate

Re: Read XML file with attributes

vapukov — Wed, 25 Mar 2020 11:40:19 GMT

sorry, I'm use tRESTClient,

in tRESTClient default output schema:

- statuscode

- body

- string

btw, I miss leading / in my answer

it works with "/gesmes:Envelope/Cube/Cube/Cube"

Re: Read XML file with attributes

philippert — Wed, 25 Mar 2020 11:40:36 GMT

In fact, it work with the loop as /gesmes:Envelope/Cube/Cube

Re: Read XML file with attributes

sushantk19 — Wed, 25 Mar 2020 11:44:48 GMT

yes, It is working fine now. Excellent!

Thanks a lot for your help!

Re: Read XML file with attributes

vapukov — Wed, 25 Mar 2020 11:44:54 GMT

strange, must not work

the simplest way - if work with XML/JSON - use the proper tools for them, it helps a lot to debug: