2 Replies Latest reply: Jul 13, 2012 1:03 AM by 879879 RSS

    Add element into XML using PL/SQL

    879879
      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
          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
            Thank you so much odie_63. :)