<employees>
<refval>senior</refval>
<employee>
<role>EX</role>
<ID>
<Qualification>leve1</Qualification>
<value>33</value>
<ID>
<ID>
<Qualification>leve2</Qualification>
<value>40</value>
<ID>
</employee>
</employees>
This is the XML I am trying to parse and get the content inside the value tag.
The value of the role tag can be
- EX, CR, AJ.
The Qualification tags in both other id tags can have three values:
The case when role=EX and Qualification=level1
Then I want to read the content of the corresponding value tag. Here the value of the value tag will be 33.
when role=CR and Qualification=level2 then value=level2
Currently, I am reading Id[1]/Qualification, Id[2]/Qualification values and writing case statements to match this condition. Is there is way to define multiple paths for a single column in XMLTable( with the help of the if condition, and or operators)?
Assuming your XML is actually well-formed and the qualification and value nodes are really under an ID node, then you can use multiple paths and the union set operator |, and different conditions in each path:
select x.*
from your_table t
cross join xmltable(
'/employees/employee[role="EX"]/ID[Qualification="level1"]
| /employees/employee[role="CR"]/ID[Qualification="level2"]
| /employees/employee[role="AJ"]/ID[Qualification="level3"]'
passing t.xml_data
columns
role varchar2(2) path './../role',
qualificiation varchar2(10) path 'Qualification',
value number path 'value'
) x;
The main Xpath goes down to the ID node, so you can get the role by walking back up the tree one level.
SQL Fiddle with fixed XML and additional employees to demonstrate the data coming from each one.
Unfortunately walking back up the tree doesn't work properly in that version of Oracle, but it does in 12c and later (and maybe patched versions of 11gR2); here a db<>fiddle for the same code under 18c:
ROLE QUALIFICIATION VALUE
---- -------------- -----
EX level1 33
CR level2 41
AJ level3 53