Oracle-XMLTYPE:如何更新值 [英] Oracle-XMLTYPE : How to update a value

问题描述

我有一个带有xmltype列的Oracle表，该列以以下格式存储XML

I have an Oracle table with a xmltype column that stores XML in the following format

<?xml version="1.0" encoding="WINDOWS-1252"?>
<View>
    <ReportValues>
        <SalaryValue variable="HR" value="999"/>
        <SalaryValue variable="floor" value="20"/>
    </ReportValues>
</View>

我想知道如何将变量"HR"的值从999更新为666，以及如何将变量"floor"的值更新为"SALES"

I would like to know how to update the value from 999 to 666 for variable "HR" and also the variable value from "floor" to "SALES"

推荐答案

虽然@АнатолийПредеин的答案对于10g和11g绝对正确，但需要知道updatexml已经

While the answer of @Анатолий Предеин is definitely correct for 10g and 11g one needs to be aware that updatexml has been deprecated in Oracle 12c.

从12cR1开始，推荐的XML处理方式是 XQuery更新工具.它不是特定于Oracle的，而是W3C建议书也实现了许多其他XML工具.

Since 12cR1 the recommended way to manipulate XML is XQuery Update Facility. It's not specific for Oracle but a W3C Recommendation implemented many other XML tools too.

下面您将找到一个完整的示例.但是，我不介绍XQuery的详细信息，而是将您引向以下文档:

Below you'll find a complete example. However I don't go into the details of XQuery but instead point you to the following documentation:

• 针对不耐烦的XQuery更新
• 从 Oracle XML DB替换XML节点开发人员指南

• XQuery Update for the impatient
• Replacing XML Nodes from Oracle XML DB Developer's Guide

示例设置

create table so61_t(
 id number
,xml xmltype
);

insert into so61_t values(1, '<?xml version="1.0" encoding="WINDOWS-1252"?>
<View>
    <ReportValues>
        <SalaryValue variable="HR" value="999"/>
        <SalaryValue variable="floor" value="20"/>
    </ReportValues>
</View>');

insert into so61_t values(2, '<?xml version="1.0" encoding="WINDOWS-1252"?>
<View>
    <ReportValues>
        <SalaryValue variable="HR" value="998"/>
        <SalaryValue variable="floor" value="19"/>
    </ReportValues>
</View>');

修改XML

update so61_t set xml =
xmlquery(
'copy $t := $x modify(
  (for $i in $t/View/ReportValues/SalaryValue[@variable="HR"]/@value
   return replace value of node $i with ''666'')
 ,(for $i in $t/View/ReportValues/SalaryValue[@variable="floor"]/@value
   return replace value of node $i with ''SALES'')
) return $t'
passing xml as "x" returning content
)
where id = 1
;

结果

SQL> col id for 99
SQL> col xml for a78
SQL> select id, xmlserialize(content xml as varchar2(200)) as xml from so61_t;
 ID XML
--- -------------------------------------------------
  1 <?xml version="1.0" encoding="UTF-8"?>
    <View>
      <ReportValues>
        <SalaryValue variable="HR" value="666"/>
        <SalaryValue variable="floor" value="SALES"/>
      </ReportValues>
    </View>
  2 <?xml version="1.0" encoding="UTF-8"?>
    <View>
      <ReportValues>
        <SalaryValue variable="HR" value="998"/>
        <SalaryValue variable="floor" value="19"/>
      </ReportValues>
    </View>

SQL>

这篇关于Oracle-XMLTYPE:如何更新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！