For our Oracle BizTalk interface we use an XSD file to describe the master detail table sets. Surely there are enough tools to generate the XSD file, so here is another one which I created to fit our needs. It’s easily adjustable.

It starts from the base table, searches for foreign key references which map to the primary key using the following query:

    select dco1.owner
         , dco1.table_name
         , dcc.column_name
    from   dba_constraints  dco1
         , dba_cons_columns dcc
    where  dco1.constraint_type = 'R'
    and    dco1.r_constraint_name in (select dco2.constraint_name
                                      from   dba_constraints dco2
                                      where  dco2.owner      = '[OWNER]'
                                      and    dco2.table_name = '[TABLE]'
                                      and    dco2.constraint_type = 'P')
    and    dcc.owner = dco1.owner
    and    dcc.table_name = dco1.table_name
    and    dcc.constraint_name = dco1.constraint_name;

This way, other foreign keys to unique attributes are not included in the XSD file generation, just the one’s who refer to the primary key are included.

When references are found it calls itself and so on (a maximum depth can be supplied).

For the BizTalk implementation only NUMBER or STRING element types are relevant, but it is easy to adjust the attribute generation using the CASE statement:

    select column_name as element_name
         , case when dtc.data_type = 'NUMBER' then 'type="xs:int"/>' else 'type="xs:string"/>' end as element_type
    from   dba_tab_cols dtc
    where  dtc.owner      = '[OWNER]'
    and    dtc.table_name = '[TABLE]'
    order  by dtc.column_id;

The package generates a CLOB which you can insert into a table or write to a file. In the example the CLOB is inserted into a table:

CREATE TABLE TMP_CLOB (message CLOB, datetime_col DATE);

The package:
Package PCK_GenXsd.pks specification.
Package PCK_GenXsd.pkb body.

Call it:

begin
  PCK_GenXsd.PR_GenXsd('NICENAME','OWNER','STARTINGTABLE');
end;
/