3 Replies Latest reply: Oct 14, 2013 6:08 AM by odie_63 RSS

    UPDATE XML

    user4423142

      Hi Everybody,

       

      Oracle used : 11G Release 11.2.0.3.0

       

      I want to change the value of a text node. I found two methods :

       

      - the replace value of node, cf Oracle book Best Practice XML DB page 13

      - the updateXML function, cf Oracle book ref E23094, Oracle XML & DB.

      My first question is to know the best method to choice. Each method works correctly.

      My second question is the following : if the previous value is empty, each method doesn't work, certainly because the text node text() doesn't exist. So I think in this case we have to create a new text node with the value, maybe an insert node ?

      An example would be appreciated.

       

      Thanks in advance

        • 1. Re: UPDATE XML
          odie_63

          My first question is to know the best method to choice. Each method works correctly.

          updateXML function is deprecated in 12.1, so although it won't stop working right now, I think it's better to start using the new XQuery-Update method.

           

           

          My second question is the following : if the previous value is empty, each method doesn't work, certainly because the text node text() doesn't exist. So I think in this case we have to create a new text node with the value, maybe an insert node ?

          That is correct.

           

          Using the insert primitive :

          SQL> select xmlquery(

            2           'copy $d := .

            3            modify ( insert node $val into $d/root/item )

            4            return $d'

            5           passing xmlparse(document '<root><item/></root>')

            6                 , 'new_value' as "val"

            7           returning content

            8        ) as result

            9  from dual ;

           

          RESULT

          --------------------------------------------------------------------------------

          <root><item>new_value</item></root>

           

           

          Or both replace value and insert depending on the text() node existence :

          SQL> select xmlquery(

            2           'copy $d := .

            3            modify (

            4              let $i := $d/root/item

            5              return if (empty($i/text()))

            6                       then insert node $val into $i

            7                       else replace value of node $i with $val

            8            )

            9            return $d'

          10           passing xmlparse(document '<root><item>old_value</item></root>')

          11                 , 'new_value' as "val"

          12           returning content

          13        ) as result

          14  from dual ;

           

          RESULT

          --------------------------------------------------------------------------------

          <root><item>new_value</item></root>

           

          • 2. Re: UPDATE XML
            user4423142

            HI,

             

            Sorry for this late answer.

            Thank you very much for your help.

            You sent me some weeks ago the syntax to add an attribute, that is a kind of node. I didn't retrieve this subject. Could you send me this syntax again ?

             

            Thanks again to help people in this difficult but interesting subject Oracle and XML.

            • 3. Re: UPDATE XML
              odie_63

              You can find your old posts by going to your profile page, then "content" tab.