Oracle SQL - 从带有重复节点的 XML 中提取 clob 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13783403/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL - Extracting clob value from XML with repeating nodes
提问by Greg Cox
I am attempting to run SQL on a table (called test_xmlwith a column xml_data[data type xmltype]). The column contains xml with repeating nodes (test_3). The following statement runs successfully when the node contains data of a non clob size:
我试图在表上运行 SQL（test_xml用列xml_data[数据类型 xmltype] 调用）。该列包含带有重复节点 ( test_3) 的xml 。当节点包含非clob大小的数据时，以下语句成功运行：
SELECT
extractvalue (Value (wl), '*/test_3')
FROM test_xml
, TABLE (xmlsequence (extract (xml_data, '*/record'))) wl
but fails when test_3 node contains a lot of data:
但是当 test_3 节点包含大量数据时失败：
ORA-01706: user function result value was too large
ORA-01706: 用户函数结果值太大
I amended my query:
我修改了我的查询：
SELECT
extractvalue(Value (wl), '*/test_3').getClobVal()
FROM test_xml
, TABLE (xmlsequence (extract (xml_data, '*/record'))) wl
but this fails with:
但这失败了：
ORA-22806: not an object or REF
ORA-22806: 不是对象或 REF
回答by Greg Cox
This was resolved via a response received on Oracle Forums:
这是通过在 Oracle 论坛上收到的回复解决的：
From Oracle release 11.2.0.2:
从 Oracle 11.2.0.2 版开始：
SELECT x.*
FROM test_xml t
, XMLTable(
'/*/record'
passing t.xml_data
columns
test_3 clob path 'test_3'
) x
;
My database version is 10.2.0.4 hence the following 'trick' is required:
我的数据库版本是 10.2.0.4，因此需要以下“技巧”：
SELECT dbms_xmlgen.convert(x.test_3.getClobVal(), 1) as test_3
FROM test_xml t
, XMLTable(
'/*/record'
passing t.xml_data
columns
test_3 xmltype path 'test_3/text()'
) x
;
Thanks go to odie_63 for this
感谢 odie_63 为此
