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[1]/SCHEMA_OBJ[1]/DATAOBJ_NUM[1]/text()[1]
58933
 
/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]
B
 
/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1]
2008-03-19 11:17:05
 
/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1]
2008-03-19 11:17:05
 
/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1]
2008-03-19 11:17:05
 
/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/BLOCK_NUM[1]/text()[1]
65377
 
/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/text()[1]
58933
 
/ROWSET[1]/ROW[1]/TABLE_T[1]/DATAOBJ_NUM[1]/text()[1]
58933
 
/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/OBJ_NUM[1]/text()[1]
58933
 
/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_ITEM[1]/PRECISION_NUM[1]/text(
)[1]
15
 
 
12 ROWS selected.
 
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="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"]' 
 15           ,        element_value xmltype PATH '//xd:content/text()'                       
 16         ) u 
 17  WHERE  u.element_name IS NOT NULL                                                              
 
 18  ;                                                                                      
 
ELEMENT_NAME
--------------------------------------------------------------------------------
ELEMENT_VALUE
--------------------------------------------------------------------------------
<xd :update-node xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xd:node-TYPE="t
ext" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"></xd><xd :content>B</xd>
 
B
 
SQL> col NAME  FOR a60 
SQL> col VALUE FOR a20
 
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 EXTRACT(u.element_name,'//@xd:xpath','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "NAME"                                      
  8  ,      u.element_value                                                                          AS "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="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"]' 
 15           ,        element_value xmltype PATH '//xd:content/text()'                       
 16         ) u 
 17  WHERE  u.element_name IS NOT NULL                                                                            
 18  ;                                                                                      
 
NAME                                                         VALUE
------------------------------------------------------------ --------------------
/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] B
 
.

Searching for Differences

Using this method, I tried to see if I could create a diff between both tables, in a more readable form.

SQL> SELECT XMLCONCAT(XMLDIFF(
  2          xmltype(dbms_metadata.get_xml('TABLE','A')),
  3          xmltype(dbms_metadata.get_xml('TABLE','B'))
  4         ),
  5                  XMLDIFF(
  6          xmltype(dbms_metadata.get_xml('TABLE','B')),
  7          xmltype(dbms_metadata.get_xml('TABLE','A'))
  8         ) )
  9  FROM dual;
 
XMLCONCAT(XMLDIFF(XMLTYPE(DBMS_METADATA.GET_XML('TABLE','A')),XMLTYPE(DBMS_METAD
--------------------------------------------------------------------------------
<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" xmln
s: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>58933</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCH
EMA_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]/SCH
EMA_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]/SCH
EMA_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]/SCH
EMA_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]/SCH
EMA_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]/SCH
EMA_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]/STO
RAGE[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]/STO
RAGE[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]/DAT
AOBJ_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>
 
 
<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" xmln
s: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]/SCH
EMA_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]/SCH
EMA_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]/SCH
EMA_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]/SCH
EMA_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]/SCH
EMA_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]/SCH
EMA_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]/STO
RAGE[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]/STO
RAGE[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]/DAT
AOBJ_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>
 
 
SQL> WITH tabxml AS                                                                         
  2   ( SELECT XMLCONCAT(XMLDIFF(
  3                            xmltype(dbms_metadata.get_xml('TABLE','A')),
  4                            xmltype(dbms_metadata.get_xml('TABLE','B'))
  5                           ),
  6                                    XMLDIFF(
  7                            xmltype(dbms_metadata.get_xml('TABLE','B')),
  8                            xmltype(dbms_metadata.get_xml('TABLE','A'))
  9                           ) ) xmlset
 10   FROM DUAL)                                                                    
 11  SELECT EXTRACT(u.element_name,'//@xd:xpath','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "NAME"                                         
 12  ,      u.element_value                                                                          AS "VALUE"                                                 
 13  FROM   tabxml                                                                          
 14  ,      XMLTABLE                                                                        
 15         (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")                
 16          ,'//xd:update-node'                                                                          
 17           PASSING xmlset                                                                
 18           COLUMNS  element_name  xmltype PATH '//xd:update-node[@xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"]' 
 19           ,        element_value xmltype PATH '//xd:content/text()'                       
 20         ) u 
 21  WHERE  u.element_name IS NOT NULL                                                                            
 22  ;                                                                                      
 
 
NAME                                                         VALUE
------------------------------------------------------------ --------------------
/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] B
/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] A
 
SQL> -- Being a little bit more preciese (always good for your performance)
 
SQL> WITH tabxml AS                                                                         
  2   ( SELECT XMLCONCAT(XMLDIFF(
  3                            xmltype(dbms_metadata.get_xml('TABLE','A')),
  4                            xmltype(dbms_metadata.get_xml('TABLE','B'))
  5                           ),
  6                                    XMLDIFF(
  7                            xmltype(dbms_metadata.get_xml('TABLE','B')),
  8                            xmltype(dbms_metadata.get_xml('TABLE','A'))
  9                           ) ) xmlset
 10   FROM DUAL)                                                                    
 11  SELECT EXTRACT(u.element_name,'//@xd:xpath','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "NAME"                                             
 12  ,      u.element_value                                                                 AS "VALUE"                                                     
 13  FROM   tabxml                                                                          
 14  ,      XMLTABLE                                                                        
 15         (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")                
 16          ,'/xd:xdiff/xd:update-node'                                                                          
 17           PASSING xmlset                                                                
 18           COLUMNS  element_name  xmltype PATH '/xd:update-node[@xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]"]' 
 19           ,        element_value xmltype PATH '/xd:update-node/xd:content/text()'                       
 20         ) u 
 21  WHERE  u.element_name IS NOT NULL                                                                            
 22  ;                                                                                      
 
NAME                                                         VALUE
------------------------------------------------------------ --------------------
/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] B
/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1] A
 
.

The next step now was: “what will happen if I do a diff between table A and C” (table C had an extra column, remember)?

SQL> SELECT XMLCONCAT(XMLDIFF(
  2                           xmltype(dbms_metadata.get_xml('TABLE','A')),
  3                           xmltype(dbms_metadata.get_xml('TABLE','C'))
  4                          ),
  5                                   XMLDIFF(
  6                           xmltype(dbms_metadata.get_xml('TABLE','C')),
  7                           xmltype(dbms_metadata.get_xml('TABLE','A'))
  8                          ) )
  9  FROM DUAL
 10  ;
 
XMLCONCAT(XMLDIFF(XMLTYPE(DBMS_METADATA.GET_XML('TABLE','A')),XMLTYPE(DBMS_METADATA.GET_XML('TABLE',
----------------------------------------------------------------------------------------------------
<xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdif
f.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>58935</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>58935</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/DATAOBJ_N
UM[1]/text()[1]">
    </xd><xd :content>58935</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/SCHEMA_OBJ[1]/NAME[1]/t
ext()[1]">
    </xd><xd :content>C</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 15:08:47</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 15:08:47</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 15:08:47</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>65385</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/STORAGE[1]/HWMINCR[1]/t
ext()[1]">
    </xd><xd :content>58935</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>58935</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COLS[1]/text()[1]">
    </xd><xd :content>2</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/INTCOLS[1]/text()[1]">
    </xd><xd :content>2</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/KERNELCOLS[1]/text()[1]
">
    </xd><xd :content>2</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_IT
EM[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58935</xd>
 
  <xd :append-node xd:node-TYPE="element" xd:parent-xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]">
 
    </xd><xd :content>
      <col_list_item>
        <obj_num>58935</obj_num>
        <col_num>2</col_num>
        <intcol_num>2</intcol_num>
        <segcol_num>2</segcol_num>
        <property>0</property>
        <name>EXTRA</name>
        <type_num>1</type_num>
        <length>50</length>
        <not_null>0</not_null>
        <charsetid>873</charsetid>
        <charsetform>1</charsetform>
        <base_intcol_num>2</base_intcol_num>
        <base_col_type>0</base_col_type>
        <spare1>0</spare1>
        <spare2>0</spare2>
        <spare3>50</spare3>
      </col_list_item>
    </xd>
 
 
<xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdif
f.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>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_N
UM[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]/t
ext()[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]/t
ext()[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]/COLS[1]/text()[1]">
    </xd><xd :content>1</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/INTCOLS[1]/text()[1]">
    </xd><xd :content>1</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/KERNELCOLS[1]/text()[1]
">
    </xd><xd :content>1</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST_IT
EM[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58932</xd>
 
  <xd :delete-node xd:node-TYPE="element" xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]/COL_LIST
_ITEM[2]"/>
 
 
 
SQL> col LENGTH FOR a10
 
 
SQL> WITH tabxml AS                                                                         
  2   ( SELECT XMLCONCAT(XMLDIFF(
  3                            xmltype(dbms_metadata.get_xml('TABLE','A')),
  4                            xmltype(dbms_metadata.get_xml('TABLE','C'))
  5                           ),
  6                                    XMLDIFF(
  7                            xmltype(dbms_metadata.get_xml('TABLE','C')),
  8                            xmltype(dbms_metadata.get_xml('TABLE','A'))
  9                           ) ) xmlset
 10    FROM DUAL)                                                                    
 11  SELECT EXTRACT(v.append_name,'//@xd:parent-xpath','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"')             AS "NAME"
 12  ,      EXTRACT(v.append_value,'//COL_LIST_ITEM/NAME/text()','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"')   AS "VALUE"
 13  ,      EXTRACT(v.append_value,'//COL_LIST_ITEM/LENGTH/text()','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "LENGTH"
 14  FROM   tabxml                                                                          
 15  ,      XMLTABLE                                                                        
 16         (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")                
 17          ,'/xd:xdiff/xd:append-node'                                                                          
 18           PASSING  xmlset                                                                
 19           COLUMNS  append_name  xmltype PATH '/xd:append-node[@xd:parent-xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]"]' 
 20           ,        append_value xmltype PATH '/xd:append-node/xd:content'                       
 21         ) v        
 22  WHERE  v.append_name  IS NOT NULL                                                                          
 23  ;                                                                                      
 
NAME                                                         VALUE                LENGTH
------------------------------------------------------------ -------------------- ----------
/ROWSET[1]/ROW[1]/TABLE_T[1]/COL_LIST[1]                     EXTRA                50
 
.

XMLTABLE and XQuery wizards will probably do a better job and / or people will use XMLTRANSFORM or XSLT (an XML Stylesheet) to create a better report. But it works, at least for tables, but does it also work, for instance, for procedures. So lets give it a try…

SQL> DROP PROCEDURE proc_01;
 
PROCEDURE dropped.
 
SQL> 
SQL> CREATE OR REPLACE PROCEDURE proc_01
  2  AS
  3  BEGIN
  4    NULL;
  5  END;
  6  /
 
PROCEDURE created.
 
SQL> 
SQL> DROP PROCEDURE proc_02;
 
PROCEDURE dropped.
 
SQL> 
SQL> CREATE OR REPLACE PROCEDURE proc_02
  2  AS
  3  BEGIN
  4    NULL;
  5  -- this is extra
  6  END;
  7  /
 
PROCEDURE created.
 
 
SQL> SELECT dbms_metadata.get_ddl('PROCEDURE','PROC_01') FROM dual;
 
DBMS_METADATA.GET_DDL('PROCEDURE','PROC_01')
----------------------------------------------------------------------------
 
  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_01"
AS
BEGIN
  NULL;
END;
 
 
 
SQL>  SELECT dbms_metadata.get_ddl('PROCEDURE','PROC_02') FROM dual;
 
DBMS_METADATA.GET_DDL('PROCEDURE','PROC_02')
------------------------------------------------------------------------
 
  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_02"
AS
BEGIN
  NULL;
-- this is extra
END;
 
.

Using DBMS_METADATA.GET_XML will give the following output.

SQL> SELECT dbms_metadata.get_xml('PROCEDURE','PROC_01') FROM dual;
 
DBMS_METADATA.GET_XML('PROCEDURE','PROC_01')
--------------------------------------------------------------------------------
< ?xml version="1.0"?><rowset><row>
  <procedure_t>
 <vers_major>1</vers_major>
 <vers_minor>1 </vers_minor>
 <obj_num>58937</obj_num>
 <type_num>7</type_num>
 <schema_obj>
  <obj_num>58937</obj_num>
  <owner_num>5</owner_num>
  <owner_name>SYSTEM</owner_name>
  <name>PROC_01</name>
  <namespace>1</namespace>
  <type_num>7</type_num>
  <type_name>PROCEDURE</type_name>
  <ctime>2008-03-19 16:39:34</ctime>
  <mtime>2008-03-19 16:39:34</mtime>
  <stime>2008-03-19 16:39:34</stime>
  <status>1</status>
  <flags>0</flags>
  <spare1>6</spare1>
  <spare2>65535</spare2>
  <spare3>5</spare3>
 </schema_obj>
 <source_lines>
  <source_lines_item>
   <obj_num>58937</obj_num>
   <line>1</line>
   <pre_name>0</pre_name>
   <post_name_off>18</post_name_off>
   <post_keyw>11</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>PROCEDURE proc_01
</source>
  </source_lines_item>
  <source_lines_item>
   <obj_num>58937</obj_num>
   <line>2</line>
   <pre_name>0</pre_name>
   <post_name_off>0</post_name_off>
   <post_keyw>0</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>AS
</source>
  </source_lines_item>
  <source_lines_item>
   <obj_num>58937</obj_num>
   <line>3</line>
   <pre_name>0</pre_name>
   <post_name_off>0</post_name_off>
   <post_keyw>0</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>BEGIN
</source>
  </source_lines_item>
  <source_lines_item>
   <obj_num>58937</obj_num>
   <line>4</line>
   <pre_name>0</pre_name>
   <post_name_off>0</post_name_off>
   <post_keyw>0</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>  NULL;
</source>
  </source_lines_item>
  <source_lines_item>
   <obj_num>58937</obj_num>
   <line>5</line>
   <pre_name>0</pre_name>
   <post_name_off>0</post_name_off>
   <post_keyw>0</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>END;</source>
  </source_lines_item>
 </source_lines>
</procedure_t>
</row></rowset>
 
 
 
SQL> SELECT dbms_metadata.get_xml('PROCEDURE','PROC_02') FROM dual;
 
DBMS_METADATA.GET_XML('PROCEDURE','PROC_02')
--------------------------------------------------------------------------------
< ?xml version="1.0"?><rowset><row>
  <procedure_t>
 <vers_major>1</vers_major>
 <vers_minor>1 </vers_minor>
 <obj_num>58938</obj_num>
 <type_num>7</type_num>
 <schema_obj>
  <obj_num>58938</obj_num>
  <owner_num>5</owner_num>
  <owner_name>SYSTEM</owner_name>
  <name>PROC_02</name>
  <namespace>1</namespace>
  <type_num>7</type_num>
  <type_name>PROCEDURE</type_name>
  <ctime>2008-03-19 16:39:34</ctime>
  <mtime>2008-03-19 16:39:34</mtime>
  <stime>2008-03-19 16:39:34</stime>
  <status>1</status>
  <flags>0</flags>
  <spare1>6</spare1>
  <spare2>65535</spare2>
  <spare3>5</spare3>
 </schema_obj>
 <source_lines>
  <source_lines_item>
   <obj_num>58938</obj_num>
   <line>1</line>
   <pre_name>0</pre_name>
   <post_name_off>18</post_name_off>
   <post_keyw>11</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>PROCEDURE proc_02
</source>
  </source_lines_item>
  <source_lines_item>
   <obj_num>58938</obj_num>
   <line>2</line>
   <pre_name>0</pre_name>
   <post_name_off>0</post_name_off>
   <post_keyw>0</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>AS
</source>
  </source_lines_item>
  <source_lines_item>
   <obj_num>58938</obj_num>
   <line>3</line>
   <pre_name>0</pre_name>
   <post_name_off>0</post_name_off>
   <post_keyw>0</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>BEGIN
</source>
  </source_lines_item>
  <source_lines_item>
   <obj_num>58938</obj_num>
   <line>4</line>
   <pre_name>0</pre_name>
   <post_name_off>0</post_name_off>
   <post_keyw>0</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>  NULL;
</source>
  </source_lines_item>
  <source_lines_item>
   <obj_num>58938</obj_num>
   <line>5</line>
   <pre_name>0</pre_name>
   <post_name_off>0</post_name_off>
   <post_keyw>0</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>-- this is extra
</source>
  </source_lines_item>
  <source_lines_item>
   <obj_num>58938</obj_num>
   <line>6</line>
   <pre_name>0</pre_name>
   <post_name_off>0</post_name_off>
   <post_keyw>0</post_keyw>
   <pre_name_len>0</pre_name_len>
   <source>END;</source>
  </source_lines_item>
 </source_lines>
</procedure_t>
</row></rowset>
 
.

Appling the same XMLDIFF method as demonstrated before will give the following.

SQL> SELECT XMLDIFF(
  2                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
  3                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_02'))
  4                ) AS "DIFFERENCES"
  5  FROM dual;
 
DIFFERENCES
----------------------------------------------------------------------------------------------------
<xd :xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdif
f.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]/PROCEDURE_T[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58938</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58938</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]">
    </xd><xd :content>PROC_02</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58938</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[1]/SOURCE[1]/text()[1]">
    </xd><xd :content>PROCEDURE proc_02
</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[2]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58938</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[3]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58938</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58938</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]/SOURCE[1]/text()[1]">
    </xd><xd :content>  NULL;
</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58938</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/SOURCE[1]/text()[1]">
    </xd><xd :content>-- this is extra
</xd>
 
  <xd :append-node xd:node-TYPE="element" xd:parent-xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]">
    </xd><xd :content>
      <source_lines_item>
        <obj_num>58938</obj_num>
        <line>6</line>
        <pre_name>0</pre_name>
 
        <post_name_off>0</post_name_off>
        <post_keyw>0</post_keyw>
        <pre_name_len>0</pre_name_len>
        <source>END;</source>
      </source_lines_item>
    </xd>
 
SQL> SET LINES 200
SQL> col name FOR a100
SQL> col VALUE FOR a50
 
SQL> WITH tabxml AS                                                                         
  2   ( SELECT XMLDIFF(
  3                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
  4                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_02'))
  5                ) xmlset
  6  FROM dual)                                                                    
  7  SELECT EXTRACT(u.element_name,'//@xd:xpath','xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"') AS "NAME"                                                                  
  8  ,      u.element_value                                                                 AS "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="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/SOURCE[1]/text()[1]"]' 
 15           ,        element_value xmltype PATH '//xd:content/text()'                       
 16         ) u 
 17  WHERE  u.element_name IS NOT NULL                                                                            
 18  ;        
 
NAME                                                                                                 VALUE
---------------------------------------------------------------------------------------------------- --------------------------------------------------
/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/SOURCE[1]/text()[1]            -- this is extra
 
.

What will happen if we make it a little bit more interesting…?

SQL> DROP PROCEDURE proc_03;
 
PROCEDURE dropped.
 
SQL> CREATE OR REPLACE PROCEDURE proc_03
  2  AS
  3  BEGIN
  4  
  5   NULL;
  6   
  7   dbms_output.put_line('Hello World');
  8   
  9  END;
 10  /
 
PROCEDURE created.
 
SQL> SELECT XMLDIFF(
  2                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
  3                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_03'))
  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]/PROCEDURE_T[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58939</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58939</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/NAME[1]/text()[1]">
    </xd><xd :content>PROC_03</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/CTIME[1]/text()[1]">
    </xd><xd :content>2008-03-19 17:08:26</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/MTIME[1]/text()[1]">
    </xd><xd :content>2008-03-19 17:08:26</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SCHEMA_OBJ[1]/STIME[1]/text()[1]">
    </xd><xd :content>2008-03-19 17:08:26</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[1]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58939</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[1]/SOURCE[1]/text()[1]">
    </xd><xd :content>PROCEDURE proc_03
</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[2]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58939</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[3]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58939</xd>
 
  <xd :insert-node-BEFORE xd:node-TYPE="element" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]">
    </xd><xd :content>
      <source_lines_item>
        <obj_num>58939</obj_num>
        <line>4</line>
        <pre_name>0</pre_name>
        <post_name_off>0</post_name_off>
        <post_keyw>0</post_keyw>
        <pre_name_len>0</pre_name_len>
        <SOURCE />
      </source_lines_item>
    </xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58939</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]/LINE[1]/text()[1]">
    </xd><xd :content>5</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[4]/SOURCE[1]/text()[1]">
    </xd><xd :content> NULL;
</xd>
 
  <xd :insert-node-BEFORE xd:node-TYPE="element" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]">
    </xd><xd :content>
      <source_lines_item>
        <obj_num>58939</obj_num>
        <line>6</line>
        <pre_name>0</pre_name>
        <post_name_off>0</post_name_off>
        <post_keyw>0</post_keyw>
        <pre_name_len>0</pre_name_len>
        <SOURCE />
      </source_lines_item>
    </xd>
 
  <xd :insert-node-BEFORE xd:node-TYPE="element" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]">
    </xd><xd :content>
      <source_lines_item>
        <obj_num>58939</obj_num>
        <line>7</line>
        <pre_name>0</pre_name>
        <post_name_off>0</post_name_off>
        <post_keyw>0</post_keyw>
        <pre_name_len>0</pre_name_len>
        <source> dbms_output.put_line(&apos;Hello World&apos;);
</source>
      </source_lines_item>
    </xd>
 
  <xd :insert-node-BEFORE xd:node-TYPE="element" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]">
    </xd><xd :content>
      <source_lines_item>
        <obj_num>58939</obj_num>
        <line>8</line>
        <pre_name>0</pre_name>
        <post_name_off>0</post_name_off>
        <post_keyw>0</post_keyw>
        <pre_name_len>0</pre_name_len>
        <SOURCE />
      </source_lines_item>
    </xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/OBJ_NUM[1]/text()[1]">
    </xd><xd :content>58939</xd>
 
  <xd :update-node xd:node-TYPE="text" xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]/LINE[1]/text()[1]">
    </xd><xd :content>9</xd>
 
 
.

Be aware that I also put “empty” lines in the procedure. “Whitespace” is always tricky, so lets check.

The problem here is that lines 4,6 and 8 are described by a empty element [SOURCE/] (sorry for the [] brackets, my code plugin still has problems with XML tags). Whatever I tried, I didn’t get the wanted end result, as show below (but hopefully someone has an answer to this).

 
LINE TEXT
---- --------------------------------------------------
   4
   6
   7  dbms_output.put_line('Hello World');
   8
.

The following attempts were made by me, but didn’t gave me the wanted result.

 
SQL> WITH tabxml AS                                                                         
  2   ( SELECT XMLDIFF(
  3                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
  4                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_03'))
  5                ) xmlset
  6  FROM dual)                                                                    
  7  SELECT u.element_line  AS "LINE"
  8  ,      u.element_value AS "SOURCE"                                                         
  9  FROM   tabxml                                                                          
 10  ,      XMLTABLE                                                                        
 11         (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")                
 12          ,'//xd:xdiff'                                                                          
 13           PASSING xmlset                                                                
 14           COLUMNS  element_name  xmltype PATH '//xd:insert-node-before[@xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]"]' 
 15           ,        element_line  xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/LINE/text()'
 16           ,        element_value xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/SOURCE/text()'                       
 17         ) u 
 18  WHERE  u.element_name IS NOT NULL   
 19  ORDER BY 1 
 20  ;           
SELECT u.element_line  AS "LINE"
       *
ERROR at line 7:
ORA-22950: cannot ORDER objects WITHOUT MAP OR ORDER method
 
SQL> WITH tabxml AS                                                                         
  2   ( SELECT XMLDIFF(
  3                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
  4                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_03'))
  5                ) xmlset
  6  FROM dual)                                                                    
  7  SELECT POSITION
  8  ,      u.element_line  AS "LINE"
  9  ,      u.element_value AS "SOURCE"                                                         
 10  FROM   tabxml                                                                          
 11  ,      XMLTABLE                                                                        
 12         (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")                
 13          ,'//xd:xdiff'                                                                          
 14           PASSING xmlset                                                                
 15           COLUMNS  POSITION FOR ORDINALITY
 16           ,        element_name  xmltype PATH '//xd:insert-node-before[@xd:xpath="/ROWSET[1]/ROW[1]/PROCEDURE_T[1]/SOURCE_LINES[1]/SOURCE_LINES_ITEM[5]"]' 
 17           ,        element_line  xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/LINE/text()'
 18           ,        element_value xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/SOURCE/text()'                       
 19         ) u 
 20  WHERE  u.element_name IS NOT NULL   
 21  ORDER BY 1 
 22  ;           
 
  POSITION LINE SOURCE
---------- ---- --------------------------------------------------
         1 4678  dbms_output.put_line(&apos;Hello World&apos;);
 
 
SQL> WITH tabxml AS                                                                         
  2   ( SELECT XMLDIFF(
  3                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
  4                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_03'))
  5                ) xmlset
  6  FROM dual)                                                                    
  7  SELECT POSITION
  8  ,      u.element_line  AS "LINE"
  9  ,      u.element_value AS "SOURCE"                                                         
 10  FROM   tabxml                                                                          
 11  ,      XMLTABLE                                                                        
 12         (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")                
 13          ,'//xd:xdiff'                                                                          
 14           PASSING xmlset                                                                
 15           COLUMNS  POSITION FOR ORDINALITY
 16           ,        element_line  xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/LINE/text()'
 17           ,        element_value xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/SOURCE/text()'                       
 18         ) u 
 19  ORDER BY 1 
 20  ;           
 
  POSITION LINE SOURCE
---------- ---- --------------------------------------------------
         1 4678  dbms_output.put_line(&apos;Hello World&apos;);
 
 
SQL> WITH tabxml AS                                                                         
  2   ( SELECT XMLDIFF(
  3                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_01')),
  4                 xmltype(dbms_metadata.get_xml('PROCEDURE','PROC_03'))
  5                ) xmlset
  6  FROM dual)                                                                    
  7  SELECT POSITION
  8  ,      u.element_line  AS "LINE"
  9  ,      u.element_value AS "SOURCE"                                                         
 10  FROM   tabxml                                                                          
 11  ,      XMLTABLE                                                                        
 12         (XMLNAMESPACES ('http://xmlns.oracle.com/xdb/xdiff.xsd' AS "xd")                
 13          ,'//xd:xdiff'                                                                          
 14           PASSING xmlset                                                                
 15           COLUMNS  POSITION FOR ORDINALITY
 16           ,        element_line  xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/LINE/text()'
 17           ,        element_value xmltype PATH '//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/SOURCE/text()'             
 18         ) u 
 19  WHERE existsnode(tabxml.xmlset,'//xd:insert-node-before/xd:content/SOURCE_LINES_ITEM/SOURCE'
 20                                  ,'xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"'
 21                     ) = 1
 22  ORDER BY 1 
 23  ;           
 
  POSITION LINE SOURCE
---------- ---- --------------------------------------------------
         1 4678  dbms_output.put_line(&apos;Hello World&apos;);
 
.

Conclusion

Hmmm, mixed feelings. The principle works, but I am not yet happy enough with the end result. I guess that people (aka programmers) that are more experienced in the use with XSQL, XQuery etc can make this work. In the meantime I will try to achieve better results. If you managed and created a blog post about it. Drop your URL here via the comment facility.

I hope I have been of help (or inspired someone else – passed on the inspiration from Dominic / Laurent so to say…).

Grz

Marco

 

Further Reading

Download the used script here: