Найдите XML-тег, который присутствует несколько раз

#sql #xml #oracle

#sql #xml #Oracle

Вопрос:

Я работаю с базой данных Oracle 19c.

У меня есть таблица с полем большого двоичного объекта «MSG_BODY». Это поле содержит XML-файлы, подобные этому:

 <Body xmlns = "http://www.finnova.ch/ZV/EHF/021">
<Auftrag>
    <Auftragsinformation>
        <Auftragsidentifikation>
            <AUF_LNR>987987987987</AUF_LNR>
            <APPL_ID>9999</APPL_ID>
        </Auftragsidentifikation>
        <Auftragsreferenz>
            <EXT_REF>TEST-2020082109574181</EXT_REF>
            <EXT_AUF_REF>BA18081508D86B28</EXT_AUF_REF>
            <KD_LNR_ERF>901</KD_LNR_ERF>
        </Auftragsreferenz>
    </Auftragsinformation>
    <Zahlungsliste>
        <Zahlung>
            <Identifikation>
                <ZV_ZLG_SYS_LNR>987987987987</ZV_ZLG_SYS_LNR>
                <ZV_ZLG_LNR>1</ZV_ZLG_LNR>
            </Identifikation>
            <Referenz>
                <EXT_REF>ABCD654654654</EXT_REF>
                <EXT_REF_AUF>XX-XXX 230/99999/1</EXT_REF_AUF>
                <EXT_REF_AUF_IB>BA9999988888</EXT_REF_AUF_IB>
                <ZLG_INSTR_ID>BA999988886666</ZLG_INSTR_ID>
                <MeldungsRef>
                    <MSG_TX_ID>123123123123</MSG_TX_ID>
                    <CS_ZLG_TRACK_ID>d8047b9f-a8c7-4d74-b5c7-470510240b60</CS_ZLG_TRACK_ID>
                    <CS_SWIFTGPI_SVC_ID>001</CS_SWIFTGPI_SVC_ID>
                </MeldungsRef>
                <MeldungsRef>
                    <MSG_TX_ID_DECK>xxxxxxxxxx</MSG_TX_ID_DECK>
                </MeldungsRef>
            </Referenz>
            <Mitteilung>
                <MIT_BEGxxx</MIT_BEG>
                <MIT_BEG_XML>
                    <Ustrd>xxx</Ustrd>
                </MIT_BEG_XML>
                <PURP_CD>SALA</PURP_CD>
            </Mitteilung>
        </Zahlung>
    </Zahlungsliste>
</Auftrag>

Тег «Zahlung» может существовать несколько раз, и это нормально, но в тег «Zahlung» входит
тег «MeldungsRef». Этот тег должен существовать ноль или один раз для каждого тега «Zahlung». Это ошибка, показанная в приведенном выше XML. Теперь мне нужен запрос для выбора всех строк в таблице, которая содержит XML, где тег «MeldungsRef» встречается там несколько раз. Как я могу это сделать?

Спасибо, что помогли мне!

С уважением, mablaser

Ответ №1:

Вы ищете второе отображение MeldungsRef узла внутри Zahlung узла, поэтому вы можете искать непосредственно это. Этот запрос показывает вам первый и второй экземпляры узла, используя xmlquery() и указывая внешний вид для поиска с помощью [1] или [2] :

 select id,
  xmlquery(
    'declare default element namespace "http://www.finnova.ch/ZV/EHF/021"; (: :)
     /Body/Auftrag/Zahlungsliste/Zahlung/Referenz/MeldungsRef[1]'
    passing xmltype(msg_body)
    returning content
  ) as first,
  xmlquery(
    'declare default element namespace "http://www.finnova.ch/ZV/EHF/021"; (: :)
     /Body/Auftrag/Zahlungsliste/Zahlung/Referenz/MeldungsRef[2]'
    passing xmltype(msg_body)
    returning content
  ) as second
from your_table;

Вы могли бы поискать второй, не являющийся нулевым, но проще использовать тот же XPath с xmlexists() , чтобы проверить, существует ли второй дочерний узел:

 select id
from your_table
where xmlexists(
  'declare default element namespace "http://www.finnova.ch/ZV/EHF/021"; (: :)
   /Body/Auftrag/Zahlungsliste/Zahlung/Referenz/MeldungsRef[2]'
  passing xmltype(msg_body)
);

db<> возится с одной хорошей (один узел) и одной плохой (несколько узлов) строкой.

я получаю следующую ошибку: ORA-32512: введите ‘внешнюю переменную xquery’

Поскольку ваш базовый столбец представляет собой большой двоичный объект, вам нужно указать ему, какой это набор символов, например:

   passing xmltype(msg_body, nls_charset_id('UTF8'))

db<>fiddle.

1. Большое спасибо, что помогли мне! К сожалению, я получаю следующую ошибку: ORA-32512: тип ‘внешняя переменная xquery’ неизвестен, что я делаю не так?

2. @mablaser — смотрите обновление; ему необходимо знать набор символов для содержимого большого двоичного объекта.