Извлечение 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, как показано ниже:

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 , мы получаем значение Amount из XML.

Есть необязательные узлы S_DAYS с S_DAYS с PCODE=Child1 или PCODE=Child2

Когда узел PCODE для Child1 или Child2 присутствует, мы должны сами применить для существующих строк.

Фактический результат:

Ожидаемый результат:

Любая помощь будет высоко ценится. Спасибо.

ajmalmhd04 17.02.2021 источник

Ответы (1)

arrow_upward
2
arrow_downward

Вы можете вернуться к родственному узлу студента s_days:

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, если вы всегда хотите их видеть, даже если нет узлов-студентов:

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.

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;

Любой из них дает тот же результат с вашими образцами данных:

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

Alex Poole 17.02.2021

comment

Вы спасли мой целый день. - ajmalmhd04; 17.02.2021

comment

Возможно ли иметь несколько условий для получения конечного значения: например: если узел ‹S_DAYS PCODE=Child1 MYFLAG=YES› Затем ---› child1_amount number path './PlanCode/S_DAYS[@PCODE=Child1] И . /PlanCode/S_Days[@MYFLAG=YES]/Дополнительный тариф/Дополнительный тариф/@Сумма', - ajmalmhd04; 17.02.2021

comment

@ ajmalmhd04 - да, у вас просто лишние скобки. Средний бит должен быть S_DAYS[@PCODE="Child2" and @MYFLAG="YES"]. db‹›fiddle - Alex Poole; 17.02.2021

comment

Благодаря тонну!!! - ajmalmhd04; 17.02.2021

comment

похоже на и условие , я пытался сделать с условием ИЛИ, но столкнулся с исключением. Не могли бы вы проверить ниже лучшее решение для подсчета дочерних узлов в одной строке с использованием условия ИЛИ или лучшего маршрута: noreferrer">dbfiddle.uk/ - ajmalmhd04; 17.02.2021

comment

Не знаю, что с этим не так. Хотя вы можете просто проверить, являются ли дочерние суммы нулевыми. В любом случае, вам нужно задать новый вопрос, если у вас возникла новая проблема. - Alex Poole; 17.02.2021

Извлечение Oracle XMLTYPE на основе значения и условия

Ответы (1)

Вопросы по теме