This discussion is archived
2 Replies Latest reply: Jul 12, 2012 11:03 PM by 879879 RSS

Add element into XML using PL/SQL

879879 Newbie
Currently Being Moderated
I have CLOB column contain XML data(more than 3000 character)
something like this
--------------------------------------------------------------
<order>
     <header>
          <line1></line1>
          <line2>
               <name1></name2>
               <name1></name2>
          </line2>
     </header>
     <detail>
     </detail>
</order>
----------------------------------------------------------------
Could anyone tell me how can I add an *<line3></line3>* under an element *<header>*.

Thank you!
Fah
  • 1. Re: Add element into XML using PL/SQL
    odie_63 Guru
    Currently Being Moderated
    Here's one way :
    SQL> create table my_table ( my_clob clob );
     
    Table created
     
    SQL> 
    SQL> insert into my_table (my_clob)
      2  values ('<order>
      3  <header>
      4  <line1></line1>
      5  <line2>
      6  <name1></name1>
      7  <name2></name2>
      8  </line2>
      9  </header>
     10  <detail>
     11  </detail>
     12  </order>')
     13  ;
     
    1 row inserted
     
    SQL> select appendchildxml(
      2           xmltype(my_clob)
      3         , '/order/header'
      4         , xmlelement("line3")
      5         ).getclobval()
      6  from my_table
      7  ;
     
    APPENDCHILDXML(XMLTYPE(MY_CLOB
    --------------------------------------------------------------------------------
    <order><header><line1/><line2><name1/><name2/></line2><line3/></header><detail>
    </detail></order>
     
    If you want to actually update the column with the new content then :
    update my_table
    set my_clob = appendchildxml(
                    xmltype(my_clob)
                  , '/order/header'
                  , xmlelement("line3")
                  ).getclobval()
    where ...
  • 2. Re: Add element into XML using PL/SQL
    879879 Newbie
    Currently Being Moderated
    Thank you so much odie_63. :)

Legend

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