基于值和条件的 Oracle XMLTYPE 提取 [英] Oracle XMLTYPE extract based on value and 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
我有如下的 XML 查询示例:
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;
XML 预期具有 S_DAYS 节点和 STUDENT ->DIVISION ，我们从 XML 中获取 Amount 值.
The XML expected to have S_DAYS node with STUDENT -> DIVISION , we fetch Amount value from the XML.
有可选节点 S_DAYS 和 S_DAYS，PCODE=Child1"；或 PCODE=Child2"
There are optional node(s) S_DAYS with S_DAYS with PCODE="Child1" or PCODE="Child2"
当存在 Child1 或 Child2 的节点 PCODE 时，我们必须自己申请现有行.
When node PCODE for Child1 or Child2 is present, we have to apply for existing rows itself.
实际结果:
预期结果:
任何帮助将不胜感激.谢谢.
Any help will be much appreciated. Thanks.
推荐答案
你可以走回学生 s_days 节点的兄弟节点:
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;
或者您可以从第一个 XMLTable 中获取子项，如果您始终想查看它们，即使没有学生节点:
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;
顺便说一句，当您在 12c 上时，您可以使用 cross apply 和 outer apply - 后者而不是带有虚拟 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
这篇关于基于值和条件的 Oracle XMLTYPE 提取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
