Fetching a XML without knowning the node names

[Anonymous]

Hi all,

I have to fetch a XML file like this one, where I don´t know previously the node names. How could I do that?

Searched all around, but no success.

<?xml version="1.0" encoding="UTF-8"?>
<root>
<Capitals>
   <France>Paris</France>
   <UK>London</UK>
   <Russia>Moscow</Russia>
   <Portugal>Lisboa</Portugal>
   <China>Beijing</China>
</Capitals>
</root>

The final result shoud be 2 columns:

France - Paris

Russia - Moscow

etc...

Any ideas?

Appreciate any suggestions

[nfz11 · Accepted solution]

This can be done with Xpath using the local_name() and dot keywords.

Screenshot of test job showing also the details of the tExtractXMLField with the Xpath:

Output:

Starting job XmlDynamicElementNames at 23:25 03/05/2019.

[statistics] connecting to socket on port 3875
[statistics] connected
.---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
|                                                                                        #1. tLogRow_1                                                                                        |
+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| key | value                                                                                                                                                                                 |
+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xml | <?xml version="1.0" encoding="UTF-8"?><root><Capitals><France>Paris</France><UK>London</UK><Russia>Moscow</Russia><Portugal>Lisboa</Portugal><China>Beijing</China></Capitals></root> |
+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

.------------------.
|  #1. tLogRow_2   |
+---------+--------+
| key     | value  |
+---------+--------+
| country | France |
| city    | Paris  |
+---------+--------+

.------------------.
|  #2. tLogRow_2   |
+---------+--------+
| key     | value  |
+---------+--------+
| country | UK     |
| city    | London |
+---------+--------+

.------------------.
|  #3. tLogRow_2   |
+---------+--------+
| key     | value  |
+---------+--------+
| country | Russia |
| city    | Moscow |
+---------+--------+

.--------------------.
|   #4. tLogRow_2    |
+---------+----------+
| key     | value    |
+---------+----------+
| country | Portugal |
| city    | Lisboa   |
+---------+----------+

.-------------------.
|   #5. tLogRow_2   |
+---------+---------+
| key     | value   |
+---------+---------+
| country | China   |
| city    | Beijing |
+---------+---------+

[statistics] disconnected

Job XmlDynamicElementNames ended at 23:25 03/05/2019. [exit code=0]

Attaching the export job zip also.

XmlDynamicElementNames.zip