修改XML节点值-与Oracle 12c等效的UpdateXML [英] Modify XML node value- UpdateXML equivalent for Oracle 12c

问题描述

我有一些示例代码，如下所示:

I have some sample code as follows:

WITH xtbl AS
         (SELECT 1 AS xtbl_id,
                 xmltype ('<node_root>
                        <node_1>12</node_1>
                        <node_2>233</node_2>
                        <node_3>223</node_3>
                        <node_4>234</node_4>
                   </node_root>') AS x
            FROM Dual
          UNION ALL
          SELECT 2, xmltype ('<node_root>
                        <node_1></node_1>
                        <node_2>233</node_2>
                        <node_3>223</node_3>
                        <node_4>234</node_4>
                   </node_root>')
            FROM Dual)
SELECT xtbl_id,
       x,
       Updatexml (x,
                  '/node_root/node_2',
                  NULL,
                  '/node_root/node_3',
                  NULL,
                  '/node_root/node_4',
                  NULL)
           AS xcol
  FROM xtbl
 WHERE (SELECT node_1
          FROM Xmltable ('node_root'
                         PASSING x
                         COLUMNS node_1 INTEGER PATH 'node_1'))
           IS NOT NULL;

我的要求是，每当列x中的/node_root/node_1不为null时，请将/node_root/node_2，/node_root/node_3和/node_root/node_4的值替换为null.我在SELECT查询中使用的Updatexml函数具有相同的功能.

My requirement is that whenever /node_root/node_1 in column x is not null then replace the values for /node_root/node_2, /node_root/node_3 and /node_root/node_4 to null. The Updatexml function I have used in my SELECT query does the same.

这里的问题是Updatexml在Oracle 12c中不起作用.这就是为什么我在子查询中使用Xmltable的原因，它可以完美地过滤数据，但是我无法将节点值替换为null.

The problem here is that Updatexml doesn't work in Oracle 12c. This is why I have used Xmltable in the subquery and it works perfect at filtering the data, but I am not able to replace the node values with null.

我尝试查看 Oracle文档 XQuery，但无法理解它对替换节点值有何帮助.

I tried looking at Oracle Docs for XQuery but couldn't understand how it can be helpful at replacing node values.

请提供一个描述性的例子.

Kindly provide a descriptive example.

推荐答案

Oracle文档建议使用XQuery更新XML .因此，这是第一件事.

Oracle documentation recommends to use XQuery to update XML. So it's first thing to try.

首先，可以使用具有功能的旧方法.可以使用下面的XQuery代替对XmlUpdate的调用:

First, it's possible with old approach with function. XQuery below may be used instead of call to XmlUpdate:

    XMLQuery(
      ' 
        declare function local:copy-replace($element as element()) {  
          if ($element/self::node_2) then <node_2/>
          else if ($element/self::node_3) then <node_3/>
          else if ($element/self::node_4) then <node_4/>
          else element {node-name($element)}  
                       {$element/@*, 
                        for $child in $element/node()  
                        return if ($child instance of element())  
                               then local:copy-replace($child)  
                               else $child  
                       }  
        };  
        local:copy-replace($p/*)
      '
      passing x as "p" returning content
    ) as xcol_2  

另一个更简短，更直观的变体:

Another, shorter and more intuitive variant:

    XMLQuery(
      '              
        copy $p2 := $p
        modify(
          replace value of node $p2/node_root/node_2 with "",
          replace value of node $p2/node_root/node_3 with "",
          replace value of node $p2/node_root/node_4 with ""
        )
        return $p2
      '
      passing x as "p" returning content
    ) as xcol_3

此外，只有在条件不匹配的情况下，才有可能返回修改后的XML值:

And in addition, it's possible to return a modified XML value only if condition not matched:

WITH xtbl AS
     (SELECT 1 AS xtbl_id,
             xmltype ('<node_root>
                    <node_1>12</node_1>
                    <node_2>233</node_2>
                    <node_3>223</node_3>
                    <node_4>234</node_4>
               </node_root>') AS x
        FROM Dual
      UNION ALL
      SELECT 2, xmltype ('<node_root>
                    <node_1></node_1>
                    <node_2>233</node_2>
                    <node_3>223</node_3>
                    <node_4>234</node_4>
               </node_root>')
        FROM Dual)
SELECT xtbl_id,
   x,
    XMLQuery(
      '   
        for $test in $p/*
        return 
          if( empty($p/node_root/node_1/text()) )             
            then $p
            else (
             copy $p2 := $p
              modify(
                replace value of node $p2/node_root/node_2 with "",
                replace value of node $p2/node_root/node_3 with "",
                replace value of node $p2/node_root/node_4 with ""
              )
              return $p2
           )   
      '
      passing x as "p" returning content
    ) as xcol_4
FROM xtbl

因此，有很多变体可以对XML值执行操作，但这需要对 XQuery 的更深入的了解. a>和 XPath 而不是相对简单的XmlUpdate函数...

So there are many variants to perform operations on XML values, but this requires deeper knowledge of XQuery and XPath than a relatively simple XmlUpdate function ...

这篇关于修改XML节点值-与Oracle 12c等效的UpdateXML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！