3 Replies Latest reply: Dec 17, 2012 9:38 AM by odie_63 RSS

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

    946786
      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
          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
            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
              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 ...
              ;