This discussion is archived
3 Replies Latest reply: Oct 14, 2013 4:08 AM by odie_63 RSS

UPDATE XML

user4423142 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points