This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Feb 20, 2013 11:26 PM by beta32c Go to original post RSS
  • 15. Re: How to register multiple XSD files
    mdrake Expert
    Currently Being Moderated
    Just for completeness I got the XML Schemas and run the analysis tool..

    The problem can be seen here..
    -- Initiating Object-Relational storage model optimization for XML Schemas in "/public/CCCIS/pathwaysXMLSchema"
    
    -- Processing Object Types derived from the following XML Schemas :-
    --    Admin.xsd
    --    CoTotals.xsd
    --    Event.xsd
    --    Image.xsd
    --    Salvage.xsd
    --    TotalLoss.xsd
    --    Vins.xsd
    --    corr.xsd
    --    estimate.xsd
    --    notes.xsd
    --    rate.xsd
    --    workfile.xsd
    
    -- Type "CCCIS"."WorkfileType3063_T" requires 1339 columns : Commencing Type Structure optimization -
    --   Processing "AdminComp" of type "CCCIS"."AdminComp2782_T" in type "CCCIS"."ClaimWorkfile3062_T". Columns = 459.
    
      -- Optimization Complete. --
    What this tells me is that the ComplexType that generated the SQLType WorkfileType3063_T would require 1339 columns to persist on disk. Since we have a llimit of 1000 columns that clearly won't work. So we look for the non repeating element that single-handedly contributes the most number of columns to this Type. We see that AdminComp, which is of AdminComp2782_T contributes 459 columns, no if we move that out of line we now have a set of types we can work with.
  • 16. Re: How to register multiple XSD files
    mdrake Expert
    Currently Being Moderated
    And here we go...
    SQL> --
    SQL> connect sys/oracle as sysdba
    Connected.
    SQL> --
    SQL> set define on
    SQL> set timing on
    SQL> --
    SQL> def XMLDIR = &1
    SQL> --
    SQL> def USERNAME = OTNTEST
    SQL> --
    SQL> def PASSWORD = &USERNAME
    SQL> --
    SQL> def USER_TABLESPACE = USERS
    SQL> --
    SQL> def TEMP_TABLESPACE = TEMP
    SQL> --
    SQL> drop user &USERNAME cascade
      2  /
    old   1: drop user &USERNAME cascade
    new   1: drop user OTNTEST cascade
    
    User dropped.
    
    Elapsed: 00:00:06.58
    SQL> grant create any directory, drop any directory, connect, resource, alter session, create view, unlimited tablespace to &USERNAME identified by &PASSWORD
      2  /
    old   1: grant create any directory, drop any directory, connect, resource, alter session, create view, unlimited tablespace to &USERNAME identified by &PASSWORD
    new   1: grant create any directory, drop any directory, connect, resource, alter session, create view, unlimited tablespace to OTNTEST identified by OTNTEST
    
    Grant succeeded.
    
    Elapsed: 00:00:00.03
    SQL> alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
      2  /
    old   1: alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
    new   1: alter user OTNTEST default tablespace USERS temporary tablespace TEMP
    
    User altered.
    
    Elapsed: 00:00:00.00
    SQL> set long 100000 pages 0 lines 256 trimspool on timing on
    SQL> --
    SQL> connect &USERNAME/&PASSWORD
    Connected.
    SQL> --
    SQL> create or replace directory XMLDIR as '&XMLDIR'
      2  /
    old   1: create or replace directory XMLDIR as '&XMLDIR'
    new   1: create or replace directory XMLDIR as 'C:\xdb\customers\CCCIS\tc'
    
    Directory created.
    
    Elapsed: 00:00:00.00
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'commonType.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'commonType.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.80
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'Admin.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'Admin.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.93
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'CoTotals.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'CoTotals.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.79
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'Event.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'Event.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.22
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'Image.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'Image.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.13
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'Salvage.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'Salvage.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.42
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'TotalLoss.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'TotalLoss.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.29
    SQL> declare
      2    V_XML_SCHEMA_NAME     VARCHAR2(700) := 'Vins.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'Vins.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    -- DOM Fidelity enabled due to presence of mixed text, substitution group heads, or repeating choice structures in complex type defintion :-
     11  
     12    DBMS_XMLSCHEMA_ANNOTATE.enableMaintainDOM(V_XML_SCHEMA,'RefurbMgr',TRUE);
     13  
     14    select /*+ NO_XML_QUERY_REWRITE */
     15             XMLQuery(
     16            'declare namespace xdb = "http://xmlns.oracle.com/xdb"; (: :)
     17             copy $NEWSCH := $SCHEMA modify (
     18                              let $MODEL := $NEWSCH/xs:schema/xs:complexType[11]/xs:all
     19                              return (
     20                                replace value of node $MODEL/xs:element[2]/xs:complexType/@xdb:maintainDOM with "false",
     21                                replace value of node $MODEL/xs:element[3]/xs:complexType/@xdb:maintainDOM with "false",
     22                                replace value of node $MODEL/xs:element[4]/xs:complexType/@xdb:maintainDOM with "false"
     23                              )
     24                            )
     25              return $NEWSCH'
     26            passing V_XML_SCHEMA as "SCHEMA"
     27            returning content
     28             )
     29        into V_XML_SCHEMA
     30        from dual;
     31  
     32    DBMS_XMLSCHEMA.registerSchema(
     33        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     34       ,SCHEMADOC      => V_XML_SCHEMA
     35       ,LOCAL           => TRUE
     36       ,GENTYPES      => TRUE
     37       ,GENTABLES      => TRUE
     38       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     39    );
     40  
     41  end;
     42  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.92
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'corr.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'corr.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.18
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'estimate.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'estimate.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:03.90
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'notes.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'notes.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.07
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'rate.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'rate.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    DBMS_XMLSCHEMA.registerSchema(
     11        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     12       ,SCHEMADOC      => V_XML_SCHEMA
     13       ,LOCAL           => TRUE
     14       ,GENTYPES      => TRUE
     15       ,GENTABLES      => TRUE
     16       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     17    );
     18  
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.57
    SQL> declare
      2    V_XML_SCHEMA_NAME       VARCHAR2(700) := 'workfile.xsd';
      3    V_XML_SCHEMA          XMLType       := xmlType(BfileName('XMLDIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
      4    V_SCHEMA_LOCATION_HINT     VARCHAR2(700) := 'workfile.xsd';
      5  begin
      6    DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
      7    DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
      8    DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
      9  
     10    -- Out-of-Line mappings for 1000 Column optimization :-
     11  
     12    DBMS_XMLSCHEMA_ANNOTATE.setOutOfLine(V_XML_SCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'WorkfileType', 'AdminComp','ADMINCOMP_XML');
     13  
     14    DBMS_XMLSCHEMA.registerSchema(
     15        SCHEMAURL      => V_SCHEMA_LOCATION_HINT
     16       ,SCHEMADOC      => V_XML_SCHEMA
     17       ,LOCAL           => TRUE
     18       ,GENTYPES      => TRUE
     19       ,GENTABLES      => TRUE
     20       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     21    );
     22  
     23  end;
     24  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:02.86
    SQL> -- Table creation for namespace "http://www.cccis.com/Pathways/Workfile"
    SQL> CREATE TABLE "PWAYWORKFILE_TABLE" of XMLTYPE
      2  XMLTYPE STORE AS OBJECT RELATIONAL
      3  XMLSCHEMA "workfile.xsd" ELEMENT "PwayWorkfile"
      4  /
    
    Table created.
    
    Elapsed: 00:00:09.61
    SQL> desc PWAYWORKFILE_TABLE
     Name                                                                                                                                               Null?    Type
     -------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------------------------------
    TABLE of SYS.XMLTYPE(XMLSchema "workfile.xsd" Element "PwayWorkfile") STORAGE Object-relational TYPE "WorkfileType1677_T"
    
    SQL> --
    SQL> quit
  • 17. Re: How to register multiple XSD files
    mdrake Expert
    Currently Being Moderated
    Also worth noting is that the analysis identified that two of the elements defined in the XML Schema act as the head of a substitution group. This means that we need to enable DOM Fidelity on any complex type that contains these elements.
  • 18. Re: How to register multiple XSD files
    beta32c Newbie
    Currently Being Moderated
    Thanks for the help. We are now able to register the Schama
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points