1 2 Previous Next 18 Replies Latest reply: Feb 21, 2013 1:26 AM by beta32c Go to original post RSS
      • 15. Re: How to register multiple XSD files
        mdrake
        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
          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
            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
              Thanks for the help. We are now able to register the Schama
              1 2 Previous Next