Oracle Xmltype Extract Based On Value And Condition

SELECT * FROM v$version; Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production 'CORE 12.1.0.2.0 Production' TNS for

Solution 1:

You can walk back up to the sibling of the student s_days node:

select h.PlanCodeCode, b.amount, b.pcode, b.child1_amount, b.child2_amount
 from   t
    crossjoin
    xmltable(xmlnamespaces(default'http://www.w3.org/2001/XMLSchema'),
             '/SSO_XML'
             passing t.xml
             columns PlanCodeCode varchar2(100)  path './PlanCode/@PlanCodeCode',
                     attributes xmltype path './PlanCode'
            ) h
    leftjoin xmltable(xmlnamespaces(default'http://www.w3.org/2001/XMLSchema'),
             'PlanCode/S_DAYS/STUDENT/DIVISION'
             passing h.attributes
             columns node_level for ordinality
                    , amount number path '@Amount'
                    , pcode  varchar2(10) path './../../@PCODE'
                    , child1_amount number path './../../../S_DAYS[@PCODE="Child1"]/AdditonalFare/AdditonalFareAmount/@Amount'
                    , child2_amount number path './../../../S_DAYS[@PCODE="Child2"]/AdditonalFare/AdditonalFareAmount/@Amount'
            ) b on1=1;

Or you can get the children from the first XMLTable, if you always want to see them even if there are no student nodes:

select h.PlanCodeCode, b.amount, b.pcode, h.child1_amount, h.child2_amount
 from   t
    crossjoin
    xmltable(xmlnamespaces(default'http://www.w3.org/2001/XMLSchema'),
             '/SSO_XML'
             passing t.xml
             columns PlanCodeCode varchar2(100)  path './PlanCode/@PlanCodeCode',
                     attributes xmltype path './PlanCode',
                     child1_amount number path './PlanCode/S_DAYS[@PCODE="Child1"]/AdditonalFare/AdditonalFareAmount/@Amount',
                     child2_amount number path './PlanCode/S_DAYS[@PCODE="Child2"]/AdditonalFare/AdditonalFareAmount/@Amount'
            ) h
    leftjoin xmltable(xmlnamespaces(default'http://www.w3.org/2001/XMLSchema'),
             'PlanCode/S_DAYS/STUDENT/DIVISION'
             passing h.attributes
             columns node_level for ordinality
                    , amount number path '@Amount'
                    , pcode  varchar2(10) path './../../@PCODE'
            ) b on1=1;

Incidentally, as you're on 12c you can use cross apply and outer apply - the latter instead of the outer join with dummy on 1=1 condition.

select h.PlanCodeCode, b.amount, b.pcode, h.child1_amount, h.child2_amount
 from   t
    cross apply
    xmltable(xmlnamespaces(default 'http://www.w3.org/2001/XMLSchema'),
             '/SSO_XML'
             passing t.xml
             columns PlanCodeCode varchar2(100)  path'./PlanCode/@PlanCodeCode',
                     attributes xmltype path'./PlanCode',
                     child1_amount number path'./PlanCode/S_DAYS[@PCODE="Child1"]/AdditonalFare/AdditonalFareAmount/@Amount',
                     child2_amount number path'./PlanCode/S_DAYS[@PCODE="Child2"]/AdditonalFare/AdditonalFareAmount/@Amount'
            ) h
    outer apply xmltable(xmlnamespaces(default 'http://www.w3.org/2001/XMLSchema'),
             'PlanCode/S_DAYS/STUDENT/DIVISION'
             passing h.attributes
             columns node_level for ordinality
                    , amount number path'@Amount'
                    , pcode  varchar2(10) path'./../../@PCODE'
            ) b;

Any of those get the same result with your sample data:

PLANCODECODE | AMOUNT | PCODE | CHILD1_AMOUNT | CHILD2_AMOUNT
:-----------| -----: |:----| ------------: | ------------:
CHOICE       | 150.05 | P123  |           100 |130
CHOICE       | 250.05 | P123  |           100 |130
CHOICE       | 150.05 | P1234 |           100 |130
CHOICE       | 250.05 | P1234 |           100 |130

db<>fiddle