主页 / 问题 / 1182225
Accepted
wq55
Asked:2020-09-25 12:10:35 +0000 UTC2020-09-25 12:10:35 +0000 UTC

如何使用过程将 CLOB 类型列中的 XML 拆分为列？

  • 772

在程序中如何将列的数据CLOB转换为关系形式并将它们写在表中？

我创建了一个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.

为什么需要一个元素，而我传递一张工作表并期望收到一张表格？

sql

1 个回答

  1. Best Answer

    XQuery FLWOR 或相关的都可以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]Адрес, график
    • 5

相关问题