Extract table and column information from a dbms_metadata export file

Peter Goldthorp June 2002

The file generated for a table by dbms_metadata.get_xml contains table and column information. Individual elements in the file can be identified via xpath if the file is stored in an xmltype column. The following program is pl/sql block which outputs the table name, column names and lengths from an xmltype column. It demonstrates a mechanism for reading the column information.

The column information in the file is in a repeating group (master/detail). There are multiple column rows for each table. This makes it difficult to query column details from the file (e.g find the length of the column called 'BUG_ID'). XPath defines a sibling notation but 9.0.1 does not support it. The following program works around this by using XPath's index notation. It uses a loop to build a string which queries each column in the table e.g:

/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM[1]/NAME
/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM[2]/NAME
/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM[3]/NAME

In this example these are output via dbms_output.put_line. They could be stored in a plsql table for later processing. Note the call tmp.existsnode(col_string). This tests for the end of the column list.

declare
  tmp         sys.xmltype;
  col         sys.xmltype;
  val         varchar2(32000);

  col_found   number(1);
  index_col   number(8);
  col_string  varchar2(256);
 
begin
  select xml_content
  into tmp
  from xml_load_data
  where id=2;

  col := tmp.extract('/ROWSET/ROW/TABLE_T/SCHEMA_OBJ/NAME/text()');
  val := col.getStringVal();
  dbms_oututil.txt_prn(val);

  index_col := 1;
  col_string := '/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM['||index_col||']';
  col_found := tmp.existsnode(col_string);

  while col_found = 1 loop
    col := tmp.extract(col_string||'/NAME');
    val := col.getStringVal();
    dbms_output.put_line(val);

    col := tmp.extract(col_string||'/LENGTH');
    val := col.getStringVal();
    dbms_output.put_line(val);

    index_col := index_col + 1;
    col_string := '/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM['||index_col||']';
    col_found := tmp.existsnode(col_string);
  end loop;

end;
/