在Oracle中的CLOB列中更新xml标记 [英] Update xml tag in a CLOB column in Oracle
问题描述
我在Oracle 11g的CLOB列中有此xml值:
I have this xml value in a CLOB column in Oracle 11g:
<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>
我想为几行更新InserDate的值.
I want to update the value of InserDate for several rows.
我正在使用下面的sql命令:
I was using next below sql command:
update tmp_tab_noemail_test p1
set p1.sce_msg = updatexml(xmltype(p1.sce_msg),
'//Energy/InsertDate/text()','Not Valid').getClobVal()
但是不起作用.
您是否有一些想法只能修改InsertDate的xml标记的值?
Do you have some ideas to modify only the values of the xml tag of InsertDate?
预先感谢
推荐答案
您在顶级Energy节点中有一个名称空间,因此如果没有,则无法匹配. UPDATEXML文档显示您可以选择提供名称空间字符串
You have a namespace in your top-level Energy node, so you aren't matching without; the UPDATEXML documentation shows you can optionally supply a namespace string.
因此,您可以使用示例数据来做到这一点:
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();
之后您将得到:
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>
或者滚动较少:
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.
这篇关于在Oracle中的CLOB列中更新xml标记的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!