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