Oracle XMLTYPE extract based on value and condition

Question

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 Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

I have sample Query with XML like below:

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.*
 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 './../../@Amount[1]' --->Child1
                    , child2_amount number path './../../@Amount[2]' --->Child2
            ) b on 1=1;

The XML expected to have S_DAYS node with STUDENT -> DIVISION , we fetch Amount value from the XML.

There are optional node(s) S_DAYS with S_DAYS with PCODE="Child1" or PCODE="Child2"

When node PCODE for Child1 or Child2 is present, we have to apply for existing rows itself.

Actual Result:

Expected Result:

Any help will be much appreciated. Thanks.

Alex Poole · Accepted Answer · 2021-02-17 16:38:38Z

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

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

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

answered Feb 17, 2021 at 16:38

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ajmalmhd04 Over a year ago

You saved my complete day.

ajmalmhd04 Over a year ago

Is it possible to have multiple condition to fetch final value: for eg: if the node is <S_DAYS PCODE="Child1" MYFLAG="YES"> Then ---> child1_amount number path './PlanCode/S_DAYS[@PCODE="Child1"] AND ./PlanCode/S_Days[@MYFLAG="YES"]/AdditonalFare/AdditonalFareAmount/@Amount',

Alex Poole Over a year ago

@ajmalmhd04 - yes, you just have extra brackets. The middle bit should be S_DAYS[@PCODE="Child2" and @MYFLAG="YES"]. db<>fiddle

ajmalmhd04 Over a year ago

similar to and condition , I was trying to do with OR condition but facing exception. Could you check below for a better solution to make the count of child nodes in single line using OR condition or a better route: dbfiddle.uk/…

Alex Poole Over a year ago

No idea what is wrong with that. Though you could just test if the child amounts are both null. Anyway, you need to ask a new question if you have a new problem.

Collectives™ on Stack Overflow

Oracle XMLTYPE extract based on value and condition

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related