基于值和条件的 Oracle XMLTYPE 提取

本文介绍了基于值和条件的 Oracle XMLTYPE 提取的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

db<>fiddle

这篇关于基于值和条件的 Oracle XMLTYPE 提取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！