This discussion is archived
3 Replies Latest reply: Dec 17, 2012 7:38 AM by odie_63 RSS

Trying to Insert an XML Element into XML data stored in CLOB column

946786 Newbie
Currently Being Moderated
Hi all,

My ORACLE DB version is:

('Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production');
('PL/SQL Release 11.2.0.2.0 - Production');
('CORE 11.2.0.2.0 Production');
('TNS for Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');


I have this XML data stored in a CLOB column:

<Activity>
     <Changes>     
     </Changes>
     <Inserts>     
     </Inserts>
     <Definition>     
     </Definition>
     <Assignment TYPE="Apply">     
     </Assignment>
     <Spawned>
          <Activity>576D8CD9-57A1-8608-1563-8F6DC74BDF3C</Activity>
          <Activity>11226E79-5D24-02EB-A950-D34A9CCFB3FF</Activity>
          <Activity>DAA68DC0-CA9A-BB15-DE31-9596E19513EE</Activity>
          <Activity>93F667D6-966A-7EAD-9B70-630D9BEFDDD2</Activity>
          <Activity>FA63D9D3-86BB-3FF0-BE69-17EAA7581637</Activity>
     </Spawned>
     <SpawnedBy>AFC49BD4-5AA7-38C0-AE27-F59D16EE1B1C</SpawnedBy>
</Activity>

I am in need of some assistance in creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.

Any help is greatly appreciated.

Thanks.

Edited by: 943783 on Dec 14, 2012 12:58 PM
  • 1. Re: Trying to Insert an XML Element into XML data stored in CLOB column
    odie_63 Guru
    Currently Being Moderated
    See XML updating functions : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb04cre.htm#i1032611

    For example :
    UPDATE my_table t
    SET t.my_clob = 
          XMLSerialize(document
            insertChildXML(
              XMLParse(document t.my_clob)
            , '/Activity/Spawned'
            , 'Activity'
            , XMLElement("Activity", 'Some GUID')
            )
          )
    WHERE ...
    ;
    Although it works, there's overhead introduced by parsing the CLOB, then serializing again.
    Is there any chance you can change the CLOB to SECUREFILE binary XMLType storage instead?
    You would then be able to benefit from optimized piecewise update of the XML and improved storage.
  • 2. Re: Trying to Insert an XML Element into XML data stored in CLOB column
    946786 Newbie
    Currently Being Moderated
    Thanks Odie that works. I was wondering, rather than hardcoding the value of "Some GUID", can we perform a SQL to retrieve the value I want to use as part of the update? If so, what would that look like?

    Thanks.
  • 3. Re: Trying to Insert an XML Element into XML data stored in CLOB column
    odie_63 Guru
    Currently Being Moderated
    I was wondering, rather than hardcoding the value of "Some GUID", can we perform a SQL to retrieve the value I want to use as part of the update?
    Sure, you can use a scalar subquery (as long as it returns only one row and column) :
    UPDATE my_table t
    SET t.my_clob = 
          XMLSerialize(document
            insertChildXML(
              XMLParse(document t.my_clob)
            , '/Activity/Spawned'
            , 'Activity'
            , (
                SELECT XMLElement("Activity", guid_column)
                FROM some_table
                WHERE ...
              )
            )
          )
    WHERE ...
    ;

Legend

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