oracle 从 CLOB（包含 XML）中提取特定值，同时为表的每一行创建一个分隔字符串。某些行的 CLOB 值可能为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15139329/
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
Extract specific value from CLOB (containing XML) while creating one delimited string per row of a table. CLOB value may be null on some rows
提问by James Gallagher
Some Context: The DB is Oracle. I am trying to create one delimited string per row of a table. Some of the values of the delimited string must come from CLOBs (containing XML). The CLOBs may be null on some rows and this is where the issue lies.
一些上下文：数据库是 Oracle。我正在尝试为表格的每一行创建一个分隔字符串。分隔字符串的某些值必须来自 CLOB（包含 XML）。某些行上的 CLOB 可能为空，这就是问题所在。
For example, I have a Table: 'Item' with the following rows: 'Item_ID', 'Item_CD', 'Item_TXT' (CLOB). The table has two rows. One row has the following XML stored within 'Item_TXT', the other row's 'Item_TXT' is null.
例如，我有一个表：“Item”，其中包含以下行：“Item_ID”、“Item_CD”、“Item_TXT”（CLOB）。该表有两行。一行的“Item_TXT”中存储了以下 XML，另一行的“Item_TXT”为空。
<OuterTag>
<InnerTag>test</InnerTag>
</OuterTag>
I created the following SQL to return the first 3 elements of the delimited string:
我创建了以下 SQL 来返回分隔字符串的前 3 个元素：
SELECT 'Item%#'|| Item_ID ||'%#'|| Item_STAT_CD
FROM Item;
This worked successfully:
这工作成功：
Item%#12345%#A
项目%#12345%#A
Item%#123456%#A
项目%#123456%#A
I then tried to add the 4th element (value from CLOB).
然后我尝试添加第四个元素（来自 CLOB 的值）。
SELECT 'Item%#'|| Item_ID ||'%#'|| Item_STAT_CD
EXTRACT(XMLTYPE(Item_TXT), '//OuterTag/InnerTag/text()').getStringVal()
FROM Item;
This failed with following error:
这失败并出现以下错误：
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
I have narrowed this issue down to sometimes the Item_TXT is null and XMLTYPE() cannot handle this. To prove this I ran the following:
我已将此问题缩小到有时 Item_TXT 为空，而 XMLTYPE() 无法处理此问题。为了证明这一点，我运行了以下命令：
SELECT 'Item%#'|| Item_ID ||'%#'|| Item_STAT_CD
EXTRACT(XMLTYPE(Item_TXT), '//OuterTag/InnerTag/text()').getStringVal()
FROM Item
WHERE Item_TXT IS NOT NULL;
This worked successfully:
这工作成功：
Item%#12345%#A%#test
项目%#12345%#A%#测试
Is there any way of processing entire table, extracting value from clob if available, otherwise adding null/nothing to delimited string?
有没有办法处理整个表，如果可用，从 clob 中提取值，否则向分隔字符串添加 null/nothing？
回答by DazzaL
the reason for your error by the way is that the xmltypeconstructor is flawed with null lobs. Personally i always use xmltype.createxml(item_txt)instead. you will find it works as well without having to worry about nvletc.
顺便说一下，您出错的原因是xmltype构造函数存在空 lob 缺陷。我个人总是使用xmltype.createxml(item_txt)代替。你会发现它也能正常工作，而不必担心nvl等。
回答by A.B.Cade
Try using nvl:
尝试使用nvl：
SELECT 'Item%#'|| Item_ID ||'%#'|| Item_STAT_CD ||'%#'||
EXTRACT(XMLTYPE(nvl(Item_TXT, '<OuterTag><InnerTag/></OuterTag>')), '//OuterTag/InnerTag/text()').getStringVal()
FROM Item;
Or you can use a CASEstatement :
或者您可以使用CASE语句：
SELECT 'Item%#'|| Item_ID ||'%#'|| Item_CD||'%#'||
case when Item_TXT is not null then
EXTRACT(XMLTYPE(Item_TXT), '//OuterTag/InnerTag/text()').getStringVal() end
FROM Item;
