How to get nodename in the xmltable function

Ideas (en)
,
1

Hi all,
I am trying to get the parent node name where the selector is located as the result in the columns of the xmltable, when a multiple nodes selection is done (here I am selecting nodes with values TRANSAC_AUTOMATED and TRANSAC_MANUAL.
I’m able to get the description of the node, but not the node name.

select basename(fle.file_path) file_name, xml.*
from   files('C:\Users\myself\Desktop\', '*.xml', false)@os fle
join   read_file_text(fle.file_path, 'ISO-8859-1')@os rfe
join   xmltable
       ( '//EXPORT/REGISTER/TRANSAC_AUTOMATED/LINE'
         || '|//EXPORT/REGISTER/TRANSAC_MANUAL/LINE' 
         -- Above we select both TRANSAC_AUTOMATED and TRANSAC_MANUAL
         passing rfe.file_contents
         columns sale_type varchar2 path '../@description'
         ,       sale_type2 varchar2 ??? -- *here to put the node name: TRANSAC_AUTOMATED OR TRANSAC_MANUAL* 
         ,       amount decimal path 'AMOUNT'
         ) xml

The XML looks like this:

<EXPORT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="blabla.xsd> 
  <REGISTER description="register datas">
    <TRANSAC_AUTOMATED description="automated sales">
      <LINE number="1">
        <AMOUNT description="amount of the Transaction">30.40</AMOUNT>
      </LINE>
      <LINE number="2">
        <AMOUNT description="amount of the Transaction">340</AMOUNT>
      </LINE>
    </TRANSAC_AUTOMATED>
    <TRANSAC_MANUAL description="Manual sales">
      <LINE number="1">
        <AMOUNT description="amount of the Transaction">35.45</AMOUNT>
      </LINE>
      <LINE number="2">
        <AMOUNT description="amount of the Transaction">450</AMOUNT>
      </LINE>
    </TRANSAC_MANUAL>
  </REGISTER>
</EXPORT>
2

It would be something like the following:

select basename(fle.file_path) file_name
, xml.*
from   files('C:\path\', 'file.xml', false)@os fle
join   read_file_text(fle.file_path)@os rfe
join   xmltable
       ( '/EXPORT/REGISTER//LINE'
         passing rfe.file_contents
         columns sale_type2 varchar2 path '../local-name()'
         ) xml

However, the local-name() expression does not seem available yet. I will consult co-workers.

3

hi any fix on this ? thx.

4

After internal discussion we have found that it is actually an enhancement to the existing logic. It opens new uses. Sad news is that till the end of the year we have various projects executing and the amount of work is such that we can not fit it in. For 2021 I can’t yet tell anything meaningfull (pending some new major projects). I propose to leave this topic open for a longer period.