Modify XML node value- UpdateXML equivalent for Oracle 12c

Question

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;

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.

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.

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.

ThinkJet · Accepted Answer · 2014-01-09 13:37:45Z

3

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($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

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

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

answered Jan 9, 2014 at 13:37

ThinkJet

6,76527 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rachcha Over a year ago

The first code example works in 11gR2 as well as 12c. The second and third examples work only in 12c. Thanks!

ThinkJet Over a year ago

It's strange because I test all variants against 11.2.0.3 version of Oracle. Can you please post exact version of your Oracle (e.g. select * from v$version)?

Rachcha Over a year ago

There are two- 11.2.0.1.0 and 12.1.0.1.0, both 64bit.

ThinkJet Over a year ago

Loooks like working since 11.2.0.3. Look at this page (sorry, can't find official proof).

Rachcha Over a year ago

No problem. Your first example runs in 11.2.0.1 and that's enough for me. Thanks!

Collectives™ on Stack Overflow

Modify XML node value- UpdateXML equivalent for Oracle 12c

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related