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:
Any help will be much appreciated. Thanks.
Edit Adding new expected result:
Two most obvious solutions appear to be...
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;
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;