Oracle XMLTYPE extract first row value from node on 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

This is an enhanced version of my previous question, 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="100.05" Code="Flat" S_CODE="1" />
                <DIVISION Amount="200.05" Code="Flat" S_CODE="2" />
            </STUDENT>
        </S_DAYS>
        <S_DAYS PCODE="P1234">
            <STUDENT>
                <DIVISION Amount="300.05" Code="Flat" S_CODE="100" />
                <DIVISION Amount="400.05" Code="Flat" S_CODE="110" />
                <DIVISION Amount="500.05" Code="Flat" S_CODE="1" />
                <DIVISION Amount="600.05" Code="Flat" S_CODE="20" />
            </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.S_CODE, h.child1_amount, h.child2_amount
 , s_code_one_amount
 --, FIRST_ROW_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'
                    , S_CODE  varchar2(10) path '@S_CODE'
                    , s_code_one_amount number path '/DIVISION[S_CODE="1"]/@Amount'
                    --, first_row_amount for ordinality/[@Amount]   exception ORA-00904: : invalid identifier                   
            ) b;

I am trying to fetch first node row in one column and value based on condition where S_CODE = "1"in another column. But here is my actual and expected result:

Actual Result:

Expected Result:

Any help will be much appreciated. Thanks.

Edit Adding new expected result:

Padders · Accepted Answer · 2021-02-19 20:13:17Z

0

Two most obvious solutions appear to be...

use xquery 'if' in path to conditionally return amount, e.g.

WITH   ...
SELECT ...
FROM   ...
       XMLTABLE (
          xmlnamespaces (DEFAULT 'http://www.w3.org/2001/XMLSchema'),
          'PlanCode/S_DAYS/STUDENT/DIVISION'
          PASSING h.attributes
          COLUMNS
             amount NUMBER PATH '@Amount',
             s_code VARCHAR2 (10) PATH '@S_CODE',
             s_code_one_amount NUMBER
                PATH 'if (@S_CODE="1") then @Amount else null',
             first_row_amount NUMBER
                PATH 'if (count(preceding-sibling::*) = 0) then @Amount else null') b;

Use xquery to return node position and handle remainder in SQL, e.g.

WITH   ...
SELECT ...
       CASE
          WHEN b.s_code = '1' THEN
             b.amount
       END
          s_code_one_amount,
       CASE
          WHEN b.position = 0 THEN
             b.amount
       END
          first_row_amount
FROM   ...
       OUTER APPLY
       XMLTABLE (
          xmlnamespaces (DEFAULT 'http://www.w3.org/2001/XMLSchema'),
          'PlanCode/S_DAYS/STUDENT/DIVISION'
          PASSING h.attributes
          COLUMNS
             position NUMBER PATH 'count(preceding-sibling::*)',
             amount NUMBER PATH '@Amount',
             s_code VARCHAR2 (10) PATH '@S_CODE') b;

answered Feb 19, 2021 at 20:13

Padders

2762 silver badges5 bronze badges

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

1 Comment

ajmalmhd04 Over a year ago

Thanks so much , I think I missed one more criteria. No NULLs should be there . Can you help in filling the "else - condition " records with the same amount of what we found? other wise I had to do this via plsql. For example, in this case, 4th row in the out put table needs a value of 500.05 in s_code_one_amount . And 300.05 for first_row_amount. I spent more time doing this and no luck so far.

Collectives™ on Stack Overflow

Oracle XMLTYPE extract first row value from node on condition

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related