更新 Oracle 中 CLOB 列中的 xml 标记
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31002954/
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
Update xml tag in a CLOB column in Oracle
提问by user1037527
I have this xml value in a CLOB column in Oracle 11g:
我在 Oracle 11g 的 CLOB 列中有这个 xml 值：
<Energy xmlns="http://euroconsumers.org/notifications/2009/01/notification">
<Gender>M</Gender>
<FirstName>MAR</FirstName>
<Name>VAN HALL</Name>
<Email/><Telephone>000000000</Telephone>
<InsertDate>2013-10-09</InsertDate>
</Energy>
I want to update the value of InserDate for several rows.
我想更新几行的 InserDate 值。
I was using next below sql command:
我正在使用下面的 sql 命令：
update tmp_tab_noemail_test p1
set p1.sce_msg = updatexml(xmltype(p1.sce_msg),
'//Energy/InsertDate/text()','Not Valid').getClobVal()
But is not working.
但不工作。
Do you have some ideas to modify only the values of the xml tag of InsertDate?
你有什么想法只修改InsertDate的xml标签的值吗？
Thanks in advances
感谢提前
回答by Alex Poole
You have a namespace in your top-level Energy node, so you aren't matching without; the UPDATEXML documentationshows you can optionally supply a namespace string.
您的顶级 Energy 节点中有一个命名空间，因此您无法匹配；UPDATEXML 文档显示您可以选择提供命名空间字符串。
So you can do this, using your example data:
因此，您可以使用示例数据执行此操作：
create table tmp_tab_noemail_test (sce_msg clob);
insert into tmp_tab_noemail_test values (
'<Energy xmlns="http://euroconsumers.org/notifications/2009/01/notification">
<Gender>M</Gender>
<FirstName>MAR</FirstName>
<Name>VAN HALL</Name>
<Email/><Telephone>000000000</Telephone>
<InsertDate>2013-10-09</InsertDate>
</Energy>');
update tmp_tab_noemail_test p1
set p1.sce_msg = updatexml(xmltype(p1.sce_msg),
'/Energy/InsertDate/text()','Not Valid',
'xmlns="http://euroconsumers.org/notifications/2009/01/notification"').getClobVal();
After which you end up with:
之后你最终得到：
select sce_msg from tmp_tab_noemail_test;
SCE_MSG
--------------------------------------------------------------------------------
<Energy xmlns="http://euroconsumers.org/notifications/2009/01/notification"><Gender>M</Gender><FirstName>MAR</FirstName><Name>VAN HALL</Name><Email/><Telephone>000000000</Telephone><InsertDate>Not Valid</InsertDate></Energy>
Or with slightly less scrolling:
或者稍微减少滚动：
select XMLQuery('//*:InsertDate' passing XMLType(sce_msg) returning content) as insertdate
from tmp_tab_noemail_test;
INSERTDATE
--------------------------------------------------------------------------------
<InsertDate xmlns="http://euroconsumers.org/notifications/2009/01/notification">Not Valid</InsertDate>
You could also wildcard the update:
您还可以通配更新：
update tmp_tab_noemail_test p1
set p1.sce_msg = updatexml(xmltype(p1.sce_msg),
'/*:Energy/*:InsertDate/text()','Not Valid').getClobVal();
... but it's probably better to specify the namespace.
...但最好指定命名空间。
