oracle xml解析 路径,从CLOB内部的XML到带有路径列表的Oracle表

我使用的Oracle版本是：

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio

NLSRTL Version 10.2.0.4.0 - Production

在上一个问题中,我已经问过如何将clob转换为表格,请参阅：

我收到的答案很棒,而且对于XML来说效果不算太大.

但是如果我有一个名为MY_TABLE_ONE的表,其中一个名为MY_FIELD的字段是一个内容非常大的CLOB(例如500 KB),则以下语句将不会在合理的时间内退出：

CREATE TABLE MY_TABLE_TWO

AS

WITH PARAMS AS (SELECT XMLTYPE (MY_FIELD) FROM MY_TABLE_ONE)

SELECT ELEMENT_PATH, ELEMENT_TEXT

FROM XMLTABLE (

'

for $i in $doc/descendant-or-self::*

return

{$i/string-join(ancestor-or-self::*/name(.), ''/'')}

{$i/text()}

'

PASSING (SELECT * FROM PARAMS) AS "doc"

COLUMNS ELEMENT_PATH VARCHAR2 (4000) PATH '//element_path',

ELEMENT_TEXT VARCHAR2 (4000) PATH '//element_content'

);

有没有其他方法可以更有效的方式转换存储在CLOB列中的XML,在Oracle表中包含路径列表和相应的值？

上述说法是正确的,但最终确定需要太多时间.

非常感谢您考虑我的要求.

编辑：

我试过这个迭代解决方案,没有成功:-(

BEGIN

DECLARE

CURSOR S_CUR

IS

WITH PARAMS AS (SELECT XMLTYPE (MY_FIELD) FROM MY_TABLE_ONE)

SELECT ELEMENT_PATH, ELEMENT_TEXT

FROM XMLTABLE (

'

for $i in $doc/descendant-or-self::*

return

{$i/string-join(ancestor-or-self::*/name(.), ''/'')}

{$i/text()}

'

PASSING (SELECT * FROM PARAMS where rownum < 101) AS "doc"

COLUMNS ELEMENT_PATH VARCHAR2 (4000) PATH '//element_path',

ELEMENT_TEXT VARCHAR2 (4000) PATH '//element_content'

);

TYPE FETCH_ARRAY IS TABLE OF S_CUR%ROWTYPE;

S_ARRAY FETCH_ARRAY;

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=256';

EXECUTE IMMEDIATE 'TRUNCATE TABLE GOOFY99 DROP STORAGE';

OPEN S_CUR;

LOOP

FETCH S_CUR

BULK COLLECT INTO S_ARRAY

LIMIT 500;

FORALL I IN 1 .. S_ARRAY.COUNT

INSERT /*+APPEND */

INTO GOOFY99

VALUES S_ARRAY (I);

COMMIT;

EXIT WHEN S_CUR%NOTFOUND;

END LOOP;

CLOSE S_CUR;

COMMIT;

END;

END;

最佳答案 UPD我找到了相当大的xml文件(140 KB).我的思路：笔记本采用核心i5处理器(2400 MHz),oracle 12c在虚拟机内部,处理时间为0.38秒.

这种方法是我所知道的唯一选择.我在w3schools.com找到了xml的例子.

declare

xml_str clob := q'[<?xml version="1.0" encoding="UTF-8"?>

Empire Burlesque

Bob Dylan

USA

Columbia

10.90

1985

Hide your heart

Bonnie Tyler

UK

CBS Records

9.90

1988

]';

v_doc dbms_xmldom.domdocument;

node dbms_xmldom.domnode;

txt varchar2(4000);

type t_list is table of number index by varchar2(4000);

v_list t_list;

procedure enum_nodes(n dbms_xmldom.domnode, tag_name varchar2) is

chn dbms_xmldom.domnode;

nl dbms_xmldom.domnodelist;

begin

nl := dbms_xmldom.getchildnodes(n);

for i in 0..dbms_xmldom.getlength(nl) loop

chn := dbms_xmldom.item(nl, i);

if dbms_xmldom.getnodetype(chn) = 1 then

enum_nodes(chn, tag_name || dbms_xmldom.getnodeName(chn) || '/');

elsif dbms_xmldom.getnodetype(chn) = 3 then

v_list(tag_name || dbms_xmldom.getnodevalue(chn)) := 1;

end if;

end loop;

end;

begin

v_doc := dbms_xmldom.newdomdocument(xml_str);

node := dbms_xmldom.makenode(v_doc);

enum_nodes(node, '/');

txt := v_list.first;

while txt is not null loop

dbms_output.put_line(txt);

txt := v_list.next(txt);

end loop;

end;

/