Как разбить XML в колонке типа CLOB по столбцам с помощью процедуры?

Question

Как можно в процедуре преобразовать данные колонки CLOB в реляционную форму и записать в таблицу test2020?

Создал таблицу test2020_555, в ней поле CLOB и скопировал туда весь текст XML.

Результат в таблице test2020 должен выглядеть так:

|VERSION | DATA       | FILENAME   | N_IRP           | TEXT                       |
|1       | 13.08.2020 | G154009_...| 54009_200810_...| [21670]оформить            | 
|1       | 13.08.2020 | G154009_...| 54009_200810_...| [21672]Адрес, график работы|

XML документ:

<?xml version="1.0" encoding="windows-1251"?>
<IRP_LIST>
  <ZGLV>
    <VERSION>1.0</VERSION>
    <DATA>2020-08-13</DATA>
    <FILENAME>G154009_0820130001</FILENAME>
  </ZGLV>
  <IRP>
    <N_IRP>54009_200810_0021670</N_IRP>
    <TEXT>[21670]оформить</TEXT>
  </IRP>
  <IRP>
    <N_IRP>54009_200810_0021672</N_IRP>
    <TEXT>[21672]Адрес, график работы</TEXT>
  </IRP>
</IRP_LIST>

Целевая таблица:

create table TEST2020 (
  VERSION            VARCHAR2(1000),
  DATA               VARCHAR2(1000),
  FILENAME           VARCHAR2(1000),
  N_IRP              VARCHAR2(1000),
  TEXT               VARCHAR2(1000)
  )

Пробую так:

SELECT x.*
FROM test2020_555 t, xmltable (
    '//IRP_LIST' PASSING xmlType (t.xml) COLUMNS
    VERSION  VARCHAR2 (1000) PATH 'ZGLV/VERSION',
    DATA     VARCHAR2 (1000) PATH 'ZGLV/DATA',
    FILENAME VARCHAR2 (1000) PATH 'FILENAME', 
    N_IRP    VARCHAR2 (1000) PATH 'IRP/N_IRP',
    TEXT     VARCHAR2 (1000) PATH 'IRP/TEXT') x

Получаю ошибку:

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence   
19279. 00000 -  "XPTY0004 - XQuery dynamic type mismatch: expected singleton   sequence - got multi-item sequence"   
*Cause:    The XQuery sequence passed in had more than one item.  
*Action:   Correct the XQuery expression to return a single item sequence.

Почему ожидается один элемент, передаю же лист и ожидаю получить таблицу?

Answer 1

Пдойдут либо XQuery FLWOR, либо связанныe xmlTable, последнее обычно эффективней:

create table docs (doc) as select to_clob ('<xml [...]>') from dual
/
declare 
    cursor cur is
        select z.*, i.*
        from docs t 
        cross join xmltable (
            '//IRP_LIST/ZGLV' passing xmlType (t.doc) columns
            version varchar2 (8) path 'VERSION',
            data varchar2 (12) path 'DATA',
            filename varchar2 (16) path 'FILENAME') z 
        cross join xmltable (
            '//IRP_LIST/IRP' passing xmlType (t.doc) columns
            irpno varchar2 (16) path 'N_IRP',
            text varchar2 (32) path 'TEXT') i; 
    type currows is table of cur%rowtype;
    rows currows; 
begin
    open cur;
    fetch cur bulk collect into rows;
    close cur;
    forall i in indices of rows
        insert into test2020 values rows(i);
end;
/

Результат:

select version, data, filename, irpno, text
from test2020
/
VERSION  DATA         FILENAME         IRPNO            TEXT                            
-------- ------------ ---------------- ---------------- --------------------------------
1.0      2020-08-13   G154009_08201300 54009_200810_002 [21670]оформить                 
1.0      2020-08-13   G154009_08201300 54009_200810_002 [21672]Адрес, график

БЛОГ НА HUSL

Как разбить XML в колонке типа CLOB по столбцам с помощью процедуры?

Ответы (1 шт):