Using XMLDIFF for Database Change Management

I was triggered by a presentation of Dominic Delmolino during Hotsos 2008. He demonstrated a cool way of applying DDL database change management while making use of the Oracle database audit functionality. I combined the idea with Laurent Schneiders “out of the XML box thinking“, like for instance, his alternative use of the XMLTABLE syntax creating a pivot table. So why not do it the same way, solving “relational” problems, via XML functionality.

As said, the idea came up during Dominic Delmolino Hotsos presentation. Since Oracle 11g, the XMLDB functionality has a build in function called XMLDIFF. In Oracle 10 only a Java and C API exists, but probably could be used in the same way as described here. The XMLDIFF function compares two XML documents and captures the differences in XML conforming to an Xdiff schema. The diff document is returned as an XMLType document. Playing with Dominico’s idea, I thought, why not try to use the DBMS_METADATA.GET_XML function, transform this to an XMLType and then process it via XMLDIFF, and see if this can be useful for database change management purposes. So I tried.

So here it goes.

An example script can be downloaded here

DBMS_METADATA

The dbms_metadata has been greatly improved, also in Oracle 11g. I created 3 tables for this example and to check my findings.

SQL> SELECT * FROM v$version;
 
BANNER
----------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS FOR 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
 
SQL> SET long 10000000
SQL> SET pages 5000
SQL> SET LINES 200
 
SQL> CREATE TABLE A
  2  (id number(10));
 
TABLE created.
 
SQL>  CREATE TABLE B
  2  (id number(15));
 
TABLE created.
 
 
SQL> CREATE TABLE C
  2  (id number(10),
  3  extra varchar2(50));
 
TABLE created.
.

A lot can be achieved via DBMS_METADATA. See, for example, the overview given here on the PSOUG site. The examples are based on the 3 tables as shown here.

SQL> SELECT dbms_metadata.get_ddl('TABLE','A') FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','A')
-------------------------------------------------------------------------
 
  CREATE TABLE "SYSTEM"."A"
   (    "ID" NUMBER(10,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','B') FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','B')
---------------------------------------------------------------------------
 
  CREATE TABLE "SYSTEM"."B"
   (    "ID" NUMBER(15,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
 
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','C') FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','C')
-------------------------------------------------------------------------
 
  CREATE TABLE "SYSTEM"."C"
   (    "ID" NUMBER(10,0),
        "EXTRA" VARCHAR2(50)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
 
  TABLESPACE "SYSTEM"
 
 
SQL> SELECT dbms_metadata.compare_alter('TABLE','A','B',USER,USER) FROM dual;
 
DBMS_METADATA.COMPARE_ALTER('TABLE','A','B',USER,USER)
------------------------------------------------------------------------------
ALTER TABLE "SYSTEM"."A" MODIFY ("ID" NUMBER(15,0))
  ALTER TABLE "SYSTEM"."A" RENAME TO "B"
 
 
SQL> SELECT dbms_metadata.compare_alter('TABLE','A','C',USER,USER) FROM dual;
 
DBMS_METADATA.COMPARE_ALTER('TABLE','A','C',USER,USER)
-------------------------------------------------------------------------------
ALTER TABLE "SYSTEM"."A" ADD ("EXTRA" VARCHAR2(50))
  ALTER TABLE "SYSTEM"."A" RENAME TO "C"
 
 
SQL> SELECT dbms_metadata.compare_alter('TABLE','B','C',USER,USER) FROM dual;
 
DBMS_METADATA.COMPARE_ALTER('TABLE','B','C',USER,USER)
--------------------------------------------------------------------------------
ALTER TABLE "SYSTEM"."B" ADD ("EXTRA" VARCHAR2(50))
  ALTER TABLE "SYSTEM"."B" MODIFY ("ID" NUMBER(10,0))
  ALTER TABLE "SYSTEM"."B" RENAME TO "C"
 
.

DBMS_METADATA has also XML extensions like the following examples demonstrate.

SQL> SELECT dbms_metadata.compare_alter_xml('TABLE','A','B',USER,USER) FROM dual;
 
DBMS_METADATA.COMPARE_ALTER_XML('TABLE','A','B',USER,USER)
---------------------------------------------------------------------------------------------
 
<alter_xml xmlns="http://xmlns.oracle.com/ku" version="1.0">
   <object_type>TABLE</object_type>
   <object1>
      <schema>SYSTEM</schema>
      <name>A</name>
   </object1>
   <object2>
      <schema>SYSTEM</schema>
      <name>B</name>
   </object2>
   <alter_list>
      <alter_list_item>
         <sql_list>
            <sql_list_item>ALTER TABLE "SYSTEM"."A" MODIFY ("ID" NUMBER(15,0))</sql_list_item>
         </sql_list>
      </alter_list_item>
      <alter_list_item>
         <sql_list>
            <sql_list_item>ALTER TABLE "SYSTEM"."A" RENAME TO "B"</sql_list_item>
         </sql_list>
      </alter_list_item>
   </alter_list>
</alter_xml>
 
SQL> SELECT dbms_metadata.compare_sxml('TABLE','A','B',USER,USER) FROM dual;
 
DBMS_METADATA.COMPARE_SXML('TABLE','A','B',USER,USER)
--------------------------------------------------------------------------------
<table xmlns="http://xmlns.oracle.com/ku" version="1.0">
  <schema>SYSTEM</schema>
  <name value1="A">B</name>
  <relational_table>
    <col_list>
      <col_list_item>
        <name>ID</name>
        <datatype>NUMBER</datatype>
        <precision value1="10">15</precision>
        <scale>0</scale>
      </col_list_item>
    </col_list>
    <physical_properties>
      <heap_table>
        <segment_attributes>
          <pctfree>10</pctfree>
          <pctused>40</pctused>
          <initrans>1</initrans>
          <storage>
            <initial>65536</initial>
            <next>1048576</next>
            <minextents>1</minextents>
            <maxextents>2147483645</maxextents>
            <pctincrease>0</pctincrease>
            <freelists>1</freelists>
            <freelist_groups>1</freelist_groups>
            <buffer_pool>DEFAULT</buffer_pool>
          </storage>
          <tablespace>SYSTEM</tablespace>
          <logging>Y</logging>
        </segment_attributes>
        <compress>N</compress>
      </heap_table>
    </physical_properties>
  </relational_table>
</table>
 
 
SQL> SELECT dbms_metadata.get_sxml('TABLE', 'A', USER) FROM dual;
 
DBMS_METADATA.GET_SXML('TABLE','A',USER)
--------------------------------------------------------------------------------
 
  <table xmlns="http://xmlns.oracle.com/ku" version="1.0">
   <schema>SYSTEM</schema>
   <name>A</name>
   <relational_table>
      <col_list>
         <col_list_item>
            <name>ID</name>
            <datatype>NUMBER</datatype>
            <precision>10</precision>
            <scale>0</scale>
         </col_list_item>
      </col_list>
      <physical_properties>
         <heap_table>
            <segment_attributes>
               <pctfree>10</pctfree>
               <pctused>40</pctused>
               <initrans>1</initrans>
               <storage>
                  <initial>65536</initial>
                  <next>1048576</next>
                  <minextents>1</minextents>
                  <maxextents>2147483645</maxextents>
                  <pctincrease>0</pctincrease>
                  <freelists>1</freelists>
                  <freelist_groups>1</freelist_groups>
                  <buffer_pool>DEFAULT</buffer_pool>
               </storage>
               <tablespace>SYSTEM</tablespace>
               <logging>Y</logging>
            </segment_attributes>
            <compress>N</compress>
         </heap_table>
      </physical_properties>
   </relational_table>
</table>
.

DBMS_METADATA can also be used in different ways and or to alter its output. A small issue here is that this is only applicable to the DBMS_METADATA.GET_DDL functionality of DBMS_METADATA.

-- 
-- dbms_metadata.set_transform_param
--
-- http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_metada.htm#i1000135
 
SQL> exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'STORAGE', FALSE);
 
PL/SQL procedure successfully completed.
 
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','A') FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','A')
--------------------------------------------------------------------------------
 
  CREATE TABLE "SYSTEM"."A"
   (    "ID" NUMBER(10,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "SYSTEM"
 
 
SQL> exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'DEFAULT');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','A') FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','A')
--------------------------------------------------------------------------------
 
  CREATE TABLE "SYSTEM"."A"
   (    "ID" NUMBER(10,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
 
 
SQL> exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','A') FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','A')
--------------------------------------------------------------------------------
 
  CREATE TABLE "SYSTEM"."A"
   (    "ID" NUMBER(10,0)
   )
.

Comparing XML instances

OK, that was a brief intro. Now lets pinpoint on comparing relational objects regarding the differences in DDL. The DBMS_METADATA.SET_TRANSFORM_PARAM doesn’t work for others then GET_DDL. The following statements were immediately executed while the “SEGMENT_ATTRIBUTES” option was still FALSE in my SQL*Plus session.

-- 
-- dbms_metadata.set_transform_param ONLY for DDL statements...
--
-- http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_metada.htm#i1019414
--
 
SQL> SELECT dbms_metadata.get_xml('TABLE','A') FROM dual;
 
DBMS_METADATA.GET_XML('TABLE','A')
--------------------------------------------------------------------------------
< ?xml version="1.0"?><rowset><row>
  <table_t>
 <vers_major>1</vers_major>
 <vers_minor>2 </vers_minor>
 <obj_num>58932</obj_num>
 <schema_obj>
  <obj_num>58932</obj_num>
  <dataobj_num>58932</dataobj_num>
  <owner_num>5</owner_num>
  <owner_name>SYSTEM</owner_name>
  <name>A</name>
  <namespace>1</namespace>
  <type_num>2</type_num>
  <type_name>TABLE</type_name>
  <ctime>2008-03-19 11:16:45</ctime>
  <mtime>2008-03-19 11:16:45</mtime>
  <stime>2008-03-19 11:16:45</stime>
  <status>1</status>
  <flags>0</flags>
  <spare1>6</spare1>
  <spare2>1</spare2>
  <spare3>5</spare3>
 </schema_obj>
 <storage>
  <file_num>1</file_num>
  <block_num>65369</block_num>
  <type_num>5</type_num>
  <ts_num>0</ts_num>
  <blocks>8</blocks>
  <extents>1</extents>
  <iniexts>8</iniexts>
  <minexts>1</minexts>
  <maxexts>2147483645</maxexts>
  <extsize>128</extsize>
  <extpct>0</extpct>
  <user_num>5</user_num>
  <lists>1</lists>
  <groups>1</groups>
  <bitmapranges>2147483645</bitmapranges>
  <cachehint>0</cachehint>
  <scanhint>0</scanhint>
  <hwmincr>58932</hwmincr>
  <flags>4325377</flags>
 </storage>
 <ts_name>SYSTEM</ts_name>
 <blocksize>8192</blocksize>
 <dataobj_num>58932</dataobj_num>
 <cols>1</cols>
 <pct_free>10</pct_free>
 <pct_used>40</pct_used>
 <initrans>1</initrans>
 <maxtrans>255</maxtrans>
 <flags>1</flags>
 <audit_val>--------------------------------------</audit_val>
 <intcols>1</intcols>
 <kernelcols>1</kernelcols>
 <property>536870912</property>
 <property2>0</property2>
 <xmlschemacols>N</xmlschemacols>
 <trigflag>0</trigflag>
 <spare1>736</spare1>
 <spare6>19-MAR-08</spare6>
 <col_list>
  <col_list_item>
   <obj_num>58932</obj_num>
   <col_num>1</col_num>
   <intcol_num>1</intcol_num>
   <segcol_num>1</segcol_num>
   <property>0</property>
   <name>ID</name>
   <type_num>2</type_num>
   <length>22</length>
   <precision_num>10</precision_num>
   <scale>0</scale>
   <not_null>0</not_null>
   <charsetid>0</charsetid>
   <charsetform>0</charsetform>
   <base_intcol_num>1</base_intcol_num>
   <base_col_type>0</base_col_type>
   <spare1>0</spare1>
   <spare2>0</spare2>
   <spare3>0</spare3>
  </col_list_item>
 </col_list>
 <con0_list />
 <con1_list />
 <con2_list />
 <refpar_level>0</refpar_level>
</table_t>
 
 </row></rowset>
 
SQL> SELECT dbms_metadata.get_xml('TABLE','B') FROM dual;
 
DBMS_METADATA.GET_XML('TABLE','B')
--------------------------------------------------------------------------------
< ?xml version="1.0"?><rowset><row>
  <table_t>
 <vers_major>1</vers_major>
 <vers_minor>2 </vers_minor>
 <obj_num>58933</obj_num>
 <schema_obj>
  <obj_num>58933</obj_num>
  <dataobj_num>58933</dataobj_num>
  <owner_num>5</owner_num>
  <owner_name>SYSTEM</owner_name>
  <name>B</name>
  <namespace>1</namespace>
  <type_num>2</type_num>
  <type_name>TABLE</type_name>
  <ctime>2008-03-19 11:17:05</ctime>
  <mtime>2008-03-19 11:17:05</mtime>
  <stime>2008-03-19 11:17:05</stime>
  <status>1</status>
  <flags>0</flags>
  <spare1>6</spare1>
  <spare2>1</spare2>
  <spare3>5</spare3>
 </schema_obj>
 <storage>
  <file_num>1</file_num>
  <block_num>65377</block_num>
  <type_num>5</type_num>
  <ts_num>0</ts_num>
  <blocks>8</blocks>
  <extents>1</extents>
  <iniexts>8</iniexts>
  <minexts>1</minexts>
  <maxexts>2147483645</maxexts>
  <extsize>128</extsize>
  <extpct>0</extpct>
  <user_num>5</user_num>
  <lists>1</lists>
  <groups>1</groups>
  <bitmapranges>2147483645</bitmapranges>
  <cachehint>0</cachehint>
  <scanhint>0</scanhint>
  <hwmincr>58933</hwmincr>
  <flags>4325377</flags>
 </storage>
 <ts_name>SYSTEM</ts_name>
 <blocksize>8192</blocksize>
 <dataobj_num>58933</dataobj_num>
 <cols>1</cols>
 <pct_free>10</pct_free>
 <pct_used>40</pct_used>
 <initrans>1</initrans>
 <maxtrans>255</maxtrans>
 <flags>1</flags>
 <audit_val>--------------------------------------</audit_val>
 <intcols>1</intcols>
 <kernelcols>1</kernelcols>
 <property>536870912</property>
 <property2>0</property2>
 <xmlschemacols>N</xmlschemacols>
 <trigflag>0</trigflag>
 <spare1>736</spare1>
 <spare6>19-MAR-08</spare6>
 <col_list>
  <col_list_item>
   <obj_num>58933</obj_num>
   <col_num>1</col_num>
   <intcol_num>1</intcol_num>
   <segcol_num>1</segcol_num>
   <property>0</property>
   <name>ID</name>
   <type_num>2</type_num>
   <length>22</length>
   <precision_num>15</precision_num>
   <scale>0</scale>
   <not_null>0</not_null>
   <charsetid>0</charsetid>
   <charsetform>0</charsetform>
   <base_intcol_num>1</base_intcol_num>
   <base_col_type>0</base_col_type>
   <spare1>0</spare1>
   <spare2>0</spare2>
   <spare3>0</spare3>
  </col_list_item>
 </col_list>
 <con0_list />
 <con1_list />
 <con2_list />
 <refpar_level>0</refpar_level>
</table_t>
 
 </row></rowset>
 
.

So how useful is a XMLDIFF and/or does the idea work? After fiddling a little bit with the syntax, I got the following output.

SQL> DESC a
 Name                                      NULL?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 
SQL> DESC b
 Name                                      NULL?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(15)
 
 
SQL> exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'DEFAULT');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT XMLDIFF(
  2                 xmltype(dbms_metadata.get_xml('TABLE','A')),
  3                 xmltype(dbms_metadata.get_xml('TABLE','B'))
  4                ) AS "DIFFERENCES"
  5  FROM dual;
 
DIFFERENCES
--------------------------------------------------------------------------------
<xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" 
    xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  < ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
  </xd><xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58933</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58933</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/DATAOBJ_NUM[1]/text()[1]">
    </xd><xd :content>58933</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]">
    </xd><xd :content>B</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1]">
    </xd><xd :content>2008-03-19 11:17:05</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1]">
    </xd><xd :content>2008-03-19 11:17:05</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1]">
    </xd><xd :content>2008-03-19 11:17:05</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1]/text()[1]">
    </xd><xd :content>65377</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/text()[1]">
    </xd><xd :content>58933</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1]">
    </xd><xd :content>58933</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58933</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/PRECISION_NUM[1]/text()[1]">
    </xd><xd :content>15</xd>
 
SQL> SELECT XMLDIFF(
  2                 xmltype(dbms_metadata.get_xml('TABLE','B')),
  3                 xmltype(dbms_metadata.get_xml('TABLE','A'))
  4                ) AS "DIFFERENCES"
  5  FROM dual;
 
DIFFERENCES
--------------------------------------------------------------------------------
<xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" 
    xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  < ?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-al
gorithm="global"?>
  </xd><xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58932</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58932</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/DATAOBJ_NUM[1]/text()[1]">
    </xd><xd :content>58932</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]">
    </xd><xd :content>A</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1]">
    </xd><xd :content>2008-03-19 11:16:45</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1]">
    </xd><xd :content>2008-03-19 11:16:45</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1]">
    </xd><xd :content>2008-03-19 11:16:45</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1]/text()[1]">
    </xd><xd :content>65369</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/text()[1]">
    </xd><xd :content>58932</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1]">
    </xd><xd :content>58932</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58932</xd>
 
  <xd :update-node xd:node-type="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/PRECISION_NUM[1]/text()[1]">
    </xd><xd :content>10</xd>
 
 
.

So in all that was easy.

But be aware (as stated here):

XmlDiff ignores differences in the order of attributes while doing the comparison.
XmlDiff ignores DocType declarations. Files are not validated against the DTD.
XmlDiff ignores any differences in the namespace prefixes as long as the namespace prefixes refer to the same namespace URI. Otherwise, if two nodes have the same local name and content but differ in namespace URI, these differences are indicated.

Also be aware regarding the following examples, that XMLTABLE (and XQUERY) support XPath version 2 and the XML operators like EXTRACT, EXTRACTVALUE and EXISTSNODE, only support XPath version 1 (until now of course, it may change with new versions).

Making it more readable, I got the following output.

SQL> WITH tabxml AS                                                                         
  2   (SELECT XMLDIFF(                                                                      
  3                            xmltype(dbms_metadata.get_xml('TABLE','A')),                 
  4                            xmltype(dbms_metadata.get_xml('TABLE','B'))                  
  5                           ) xmlset                                                      
  6           FROM dual)                                                                    
  7  SELECT u.element_name                                                                  
  8  ,      u.element_value                                                              
  9  FROM   tabxml                                                                          
 10  ,      XMLTABLE                                                                        
 11         (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")                
 12          ,'//xd:update-node'                                                                    
 13           PASSING xmlset                                                                
 14           COLUMNS  element_name  xmltype PATH '//xd:update-node/@xd:xpath' 
 15           ,        element_value xmltype PATH '//xd:content/text()'                       
 16         ) u                                                                             
 17  ;                                                                                      
 
ELEMENT_NAME
--------------------------------------------------------------------------------
ELEMENT_VALUE
--------------------------------------------------------------------------------
/ROWSET[1]/ROW[1]/TABLE_T[1]/OBJ_NUM[1]/text()[1]
58933
 
/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1]/text()[1]
58933
 
/ROWSET[1]/ROW[1]/TABLE_T