Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with t(xml) as 
(
select xmltype(
'<SSO_XML
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
TimeStamp="2020-08-05T21:57:23Z" 
Target="Production" 
Version="1.0" 
TransactionIdentifier="PLAN_A" 
SequenceNmbr="123456"
    xmlns="http://www.w3.org/2001/XMLSchema">
    <PlanCode PlanCodeCode="CHOICE">
        <S_DAYS PCODE="P123">
            <STUDENT>
                <DIVISION Amount="150.05" Code="Flat" S_CODE="1" />
                <DIVISION Amount="250.05" Code="Flat" S_CODE="2" />
            </STUDENT>
        </S_DAYS>
        <S_DAYS PCODE="P1234">
            <STUDENT>
                <DIVISION Amount="150.05" Code="Flat" S_CODE="1" />
                <DIVISION Amount="250.05" Code="Flat" S_CODE="2" />
            </STUDENT>
        </S_DAYS>
          <S_DAYS PCODE="Child1">
            <AdditonalFare>
              <AdditonalFareAmount Amount="100"/>
            </AdditonalFare>
          </S_DAYS> 
          <S_DAYS PCODE="Child2">
            <AdditonalFare>
              <AdditonalFareAmount Amount="130"/>
            </AdditonalFare>
          </S_DAYS> 
    </PlanCode>
</SSO_XML>') 
 from dual
 )

select h.PlanCodeCode, b.amount, b.pcode, b.child1_amount, b.child2_amount
 from   t
    cross join
    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
    left join 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 on 1=1;


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


with t(xml) as 
(
select xmltype(
'<SSO_XML
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
TimeStamp="2020-08-05T21:57:23Z" 
Target="Production" 
Version="1.0" 
TransactionIdentifier="PLAN_A" 
SequenceNmbr="123456"
    xmlns="http://www.w3.org/2001/XMLSchema">
    <PlanCode PlanCodeCode="CHOICE">
        <S_DAYS PCODE="P123">
            <STUDENT>
                <DIVISION Amount="150.05" Code="Flat" S_CODE="1" />
                <DIVISION Amount="250.05" Code="Flat" S_CODE="2" />
            </STUDENT>
        </S_DAYS>
        <S_DAYS PCODE="P1234">
            <STUDENT>
                <DIVISION Amount="150.05" Code="Flat" S_CODE="1" />
                <DIVISION Amount="250.05" Code="Flat" S_CODE="2" />
            </STUDENT>
        </S_DAYS>
          <S_DAYS PCODE="Child1">
            <AdditonalFare>
              <AdditonalFareAmount Amount="100"/>
            </AdditonalFare>
          </S_DAYS> 
          <S_DAYS PCODE="Child2">
            <AdditonalFare>
              <AdditonalFareAmount Amount="130"/>
            </AdditonalFare>
          </S_DAYS> 
    </PlanCode>
</SSO_XML>') 
 from dual
 )

select h.PlanCodeCode, b.amount, b.pcode, h.child1_amount, h.child2_amount
 from   t
    cross join
    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
    left join 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 on 1=1;


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


with t(xml) as 
(
select xmltype(
'<SSO_XML
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
TimeStamp="2020-08-05T21:57:23Z" 
Target="Production" 
Version="1.0" 
TransactionIdentifier="PLAN_A" 
SequenceNmbr="123456"
    xmlns="http://www.w3.org/2001/XMLSchema">
    <PlanCode PlanCodeCode="CHOICE">
        <S_DAYS PCODE="P123">
            <STUDENT>
                <DIVISION Amount="150.05" Code="Flat" S_CODE="1" />
                <DIVISION Amount="250.05" Code="Flat" S_CODE="2" />
            </STUDENT>
        </S_DAYS>
        <S_DAYS PCODE="P1234">
            <STUDENT>
                <DIVISION Amount="150.05" Code="Flat" S_CODE="1" />
                <DIVISION Amount="250.05" Code="Flat" S_CODE="2" />
            </STUDENT>
        </S_DAYS>
          <S_DAYS PCODE="Child1">
            <AdditonalFare>
              <AdditonalFareAmount Amount="100"/>
            </AdditonalFare>
          </S_DAYS> 
          <S_DAYS PCODE="Child2">
            <AdditonalFare>
              <AdditonalFareAmount Amount="130"/>
            </AdditonalFare>
          </S_DAYS> 
    </PlanCode>
</SSO_XML>') 
 from dual
 )

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;


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
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