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 ('12

Solution 1:

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

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($elementas element()) {  
          if ($element/self::node_2) then <node_2/>elseif ($element/self::node_3) then <node_3/>elseif ($element/self::node_4) then <node_4/>else element {node-name($element)}  
                       {$element/@*, 
                        for$childin$element/node()  
                        returnif ($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

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

WITH xtbl AS
     (SELECT1AS 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 xFROM Dual
      UNION ALL
      SELECT2, 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/*
        returnif( 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

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 ...