我正在使用的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的字段是一个内容非常大(例如500 KB)的CLOB,则以下语句将不会在合理的时间内退出:
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 <element>
<element_path> {$i/string-join(ancestor-or-self::*/name(.), ''/'')} </element_path>
<element_content> {$i/text()}</element_content>
</element>
'
PASSING (SELECT * FROM PARAMS) AS "doc"
COLUMNS ELEMENT_PATH VARCHAR2 (4000) PATH '//element_path',
ELEMENT_TEXT VARCHAR2 (4000) PATH '//element_content'
);
有没有其他方法可以更有效地转换存储在带有路径列表和相应值的Oracle表中的CLOB列中存储的XML?
上面的陈述是正确的,但是需要太多时间才能完成。
非常感谢您考虑我的要求。
编辑:
我尝试了这种迭代解决方案,但没有成功:-(
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 <element>
<element_path> {$i/string-join(ancestor-or-self::*/name(.), ''/'')} </element_path>
<element_content> {$i/text()}</element_content>
</element>
'
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"?>
<CATALOG>
<CD>
<TITLE>Empire Burlesque</TITLE>
<ARTIST>Bob Dylan</ARTIST>
<COUNTRY>USA</COUNTRY>
<COMPANY>Columbia</COMPANY>
<PRICE>10.90</PRICE>
<YEAR>1985</YEAR>
</CD>
<CD>
<TITLE>Hide your heart</TITLE>
<ARTIST>Bonnie Tyler</ARTIST>
<COUNTRY>UK</COUNTRY>
<COMPANY>CBS Records</COMPANY>
<PRICE>9.90</PRICE>
<YEAR>1988</YEAR>
</CD>
</CATALOG>]';
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;
/
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句