Joining XMLTABLE with input table

[darkwingmcquack]

Hi All.

In Hana 2.0(sp3) there is this database view SYS.REMOTE_SOURCES

It contains all the connection properties for the various connectors configured, however oddly enough some of the values are stored in xml !?!? .. i'm trying to get them out into a relational format.

I wrote this and used the xml parser to deconstruct the elements i needed successfully from the xml contained in CONNECTION_INFO column.

However i get one (the same) row for every entry in REMOTE_SOURCES.

Oddly enough you can see the XMLTABLE is parsing the entire table set of rows correctly and clearly independently. Resulting then in a effective cartesian join with the REMOTE_SOURCES single row that was filtered.

Problem is that i cannot find a way to join the 2 tables (ie the SYS.REMOTE_SOURCES view, the other the XMLTABLE xml parsed view) in order to not cartesian join for every row in SYS.REMOTE_SOURCES.

For the query above i was expecting 1 row back (if i could join the 2 datasets)

I then thought i don't need to select from REMOTE_SOURCES, i just do the following

Which works, but now i don't have any of the relational data columns on REMOTE_SOURCES table, like the primary key. I did manage to filter XTABLE .. but only able to filter on elements in the xml not the relational columns.

any idea's or syntax i don't know of to filter on XTABLE.REMOTE_SOURCE_NAME ?

[pfefferf · Accepted solution]

One way to reach your goal is to use the function XMLEXTRACTVALUE.

select remote_source_name,
       xmlextractvalue(connection_info, '/ConnectionProperties/PropertyEntry[3]') as "Driver",
       xmlextractvalue(connection_info, '/ConnectionProperties/PropertyEntry[4]') as "Server",
       xmlextractvalue(connection_info, '/ConnectionProperties/PropertyEntry[5]') as "Port",
       xmlextractvalue(connection_info, '/ConnectionProperties/PropertyEntry[6]') as "DML_Mode"
from sys.remote_sources;

[darkwingmcquack · Accepted solution]

Perfect! thank you.