sqlxmloracle-databaseplsqlxmltype

Oracle XMLTYPE extract first row value from node on 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 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: enter image description here

Expected Result: enter image description here

Any help will be much appreciated. Thanks.

Edit Adding new expected result:

enter image description here


Solution

  • Two most obvious solutions appear to be...

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