This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Feb 20, 2013 11:26 PM by beta32c RSS

How to register multiple XSD files

beta32c Newbie
Currently Being Moderated
Hi,

I have a request to register XSD files into Oracle. In my case i understand there is a master .xsd file which calls in a number of other xsd files like below,

<xsd:schema xmlns="http://www.xxx.com/xyz/sample" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:pct="http://www.xxx.com/xyz/checktype" targetNamespace="http://www.xxx.com/xyz/sample" elementFormDefault="qualified" attributeFormDefault="unqualified" version="1.2">
     <xsd:import namespace="http://www.xxx.com/xyz/checktype" schemaLocation="checktype.xsd"/>
     <xsd:include schemaLocation="aaaa.xsd"/>
     <xsd:include schemaLocation="bbb.xsd"/>
     <xsd:include schemaLocation="ccc.xsd"/>


I looked up a number of places and I have oracle command to register XML schema using,

dbms_xmlschema.registerSchema

Can some one let me know how i can register them in Oracle when i have multiple files. I will be using Unix system to connect to oracle. Is there some place like a folder where i need to place or is it going to be multiple lines where i need to specify the list of .xsd files

Thanks,
984145
  • 1. Re: How to register multiple XSD files
    odie_63 Guru
    Currently Being Moderated
    Hi,

    Create an Oracle directory object pointing to the location of the schemas on the filesystem, e.g.
    CREATE OR REPLACE DIRECTORY xsd_dir AS '/home/oracle/xsd' ;
    Also make sure Oracle process has read privilege on this location.

    Then call DBMS_XMLSCHEMA.registerSchema for each of the schemas, in their dependency order (standalone schemas first, then the main one).
    Make sure you register them with the schemaUrl they are referred to in the import or include directives.
    DBMS_XMLSCHEMA.registerSchema(
      schemaURL => 'checktype.xsd'
    , schemaDoc => xmltype(bfilename('XSD_DIR','checktype.xsd'), nls_charset_id('AL32UTF8'))
    , local => true
    , genTypes => false
    , genTables => false
    , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
    );
    
    DBMS_XMLSCHEMA.registerSchema(
      schemaURL => 'aaaa.xsd'
    , schemaDoc => xmltype(bfilename('XSD_DIR','aaaa.xsd'), nls_charset_id('AL32UTF8'))
    , local => true
    , genTypes => false
    , genTables => false
    , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
    );
    
    ...
    If there are circular dependencies, register them with "force => true" and compile them later with DBMS_XMLSCHEMA.compileSchema.

    A couple of additional questions :
    - For what purpose do you register the schemas? Validation of XML documents, Object-Relational storage?
    - What's the database version?
  • 2. Re: How to register multiple XSD files
    beta32c Newbie
    Currently Being Moderated
    Thanks Odie...

    1) I am trying to feed in XMLs from a different system into an Oracle XMLType table.. since all our XMLs comply to the Schema and we take in like 400 tags from the XMLs I plan on to use structured storage and still have not decided on the Indexing.

    2) Then read the required tags in the XMLs and mostly load them into another table before going for another set of ETL or do the ETL steps directly as i extract the tags.

    3) I still have a big question as to first understanding the .xsd files as figure out which is standalone and which are referrenced.. I have close to 10+ .xsd files which has almost 1 or 2 other .xsd files refrenced inside. I think its going to really hard to figure out the order of having them registered in Oracle. But i will have to figure them out :)

    4) We are currently using Oracle 11g.

    Thanks for your quick response.
  • 3. Re: How to register multiple XSD files
    odie_63 Guru
    Currently Being Moderated
    1) I am trying to feed in XMLs from a different system into an Oracle XMLType table.. since all our XMLs comply to the Schema and we take in like 400 tags from the XMLs I plan on to use structured storage
    OK, then you'll have to modify these two parameters :
    , genTypes => true
    , genTables => true
    3) I still have a big question as to first understanding the .xsd files as figure out which is standalone and which are referrenced.. I have close to 10+ .xsd files which has almost 1 or 2 other .xsd files refrenced inside. I think its going to really hard to figure out the order of having them registered in Oracle. But i will have to figure them out :)
    Well, a dozen of files is not that much ;)
  • 4. Re: How to register multiple XSD files
    mdrake Expert
    Currently Being Moderated
    If you can put the files in a zip and put them somewhere I can access I have a utility I am working on that attempts to work the ordering out.
  • 5. Re: How to register multiple XSD files
    beta32c Newbie
    Currently Being Moderated
    I was some how able to register the XML Schema. But during the process had two issues.

    Issue1:-

    when i used
    genTypes => false
    , genTables => false

    I registered all my .xsd files with force=true and later on did the compile schema, during this one of my .xsd files threw an error

    ORA-31162: element or attribute "NumberItemList" has no SQLType specified
    ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 117
    ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 78
    ORA-06512: at line 2
    31162. 00000 - "element or attribute \"%s\" has no SQLType specified"
    **Cause: Schema registration was invoked with GENTYPES=false without*
    specifying a SQLType for some element or attribute
    *Action:   Specify a SQLType for the offending element or attribute and
    register the schema again


    When i looked inside the particular schema file for the element it looked like below

    <xsd:element name="NumberItemList" minOccurs="0" maxOccurs="unbounded">
    I looked up others elements and they had an sub element where the Type wasdeclared... But there were other lines where the Type was not declared too which was not caught in the Oracle error. Thanks to Odie, as you said i ran it with

    , genTypes => true
    , genTables => true

    THANKS!! the process ran without any errors on compliation.

    But later on

    when i was compling the main .xsd file the process again errored out with a new error message.

    ORA-31084: error while creating table "TEST"."Masterschema1319_TAB" for element "MasterSchema"
    ORA-01792: maximum number of columns in a table or view is 1000


    Yes I know my XML Schema indeed is very big. My Big question is is there a way to workaround this. All i could think of was just to use genTables => false for Schema files that had more number of elements.

    Please correct me if iam wrong in understanding, If we have more tables its faster to read a particular tag and extract the value and do an ETL on top of that.
  • 6. Re: How to register multiple XSD files
    beta32c Newbie
    Currently Being Moderated
    @mdrake thanks for your help. But i figured out the ordering of the XML
  • 7. Re: How to register multiple XSD files
    mdrake Expert
    Currently Being Moderated
    The utility also sorts out the 1000 column issue :). So the offer is still open
  • 8. Re: How to register multiple XSD files
    beta32c Newbie
    Currently Being Moderated
    @mdrake - Thanks but, I cant share the XMLs and they cannot be altered too.
  • 9. Re: How to register multiple XSD files
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    @mdrake works for Oracle. If needed, files could be exchanged via support.oracle.com.

    You would need to "break up" the tree structure in the appropriate places.

    Mark can help you to pinpoint where, so the 1000 column issue will be avoided. Making use of the XDB Utilities PL/SQL packages (xdb_util.zip - http://www.oracle.com/technetwork/indexes/samplecode/xmldb-sample-523617.html) to help with XMLType Object Relational storage, splits on the places needed could be done via, FOR EXAMPLE see the following code...

    The trick here is to place/create elements and complextypes out of line.
    -- -----------------------------------------------------------------------------------------------------------------------------
    -- * CMFXML.SWIFT.MT540.xsd
    -- -----------------------------------------------------------------------------------------------------------------------------
    -- Due performance registration reasons regarding the huge amount of inheritance in MT544, MT544 has to be the last to register!
    -- -----------------------------------------------------------------------------------------------------------------------------
    declare
      V_FILENAME   VARCHAR2(700) := 'CMFXML.SWIFT.MT544.xsd';
      V_SCHEMA_LOC VARCHAR2(700) := 'http://www.xmldb.nl/glq/xsd/1.0/';
      V_XMLSCHEMA  XMLTYPE := xmltype(bfilename('XMLDIR',V_FILENAME),nls_charset_id('AL32UTF8'));
      res boolean;
    begin
      dbms_xmlschema_annotate.addXDBNamespace(V_XMLSCHEMA);
      dbms_xmlschema_annotate.setDefaultTable(V_XMLSCHEMA,'CMFXML','CMFXML_MT544_TABLE');
      dbms_xmlschema_annotate.DISABLEDEFAULTTABLECREATION(V_XMLSCHEMA);
      dbms_xmlschema_annotate.DISABLEMAINTAINDOM(V_XMLSCHEMA);
      --
      select insertChildXML
       (
         V_XMLSCHEMA,
         '//xsd:element[@type="TIndicator-22F-01"]',
         '@xdb:SQLInline',
         'false',
         'xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsd="http://www.w3.org/2001/XMLSchema"'
       )
        into V_XMLSCHEMA
        from dual;
    
      select insertChildXML
       (
         V_XMLSCHEMA,
         '//xsd:element[@type="TAmount-19A-01"]',
         '@xdb:SQLInline',
         'false',
         'xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsd="http://www.w3.org/2001/XMLSchema"'
       )
        into V_XMLSCHEMA
        from dual;
    
      select insertChildXML
       (
         V_XMLSCHEMA,
         '//xsd:element[@type="TDate-98A-01"]',
         '@xdb:SQLInline',
         'false',
         'xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsd="http://www.w3.org/2001/XMLSchema"'
       )
        into V_XMLSCHEMA
        from dual;
    
      select insertChildXML
       (
         V_XMLSCHEMA,
         '//xsd:element[@type="TRate-92A-01"]',
         '@xdb:SQLInline',
         'false',
         'xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsd="http://www.w3.org/2001/XMLSchema"'
       )
        into V_XMLSCHEMA
        from dual;
    
      select insertChildXML
       (
         V_XMLSCHEMA,
         '//xsd:element[@type="TFlag-17B-01"]',
         '@xdb:SQLInline',
         'false',
         'xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsd="http://www.w3.org/2001/XMLSchema"'
       )
        into V_XMLSCHEMA
        from dual;
    
      select insertChildXML
       (
         V_XMLSCHEMA,
         '//xsd:element[@type="TQuantityOfFinancialInstrument-36B-01"]',
         '@xdb:SQLInline',
         'false',
         'xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsd="http://www.w3.org/2001/XMLSchema"'
       )
        into V_XMLSCHEMA
        from dual;
    
      select insertChildXML
       (
         V_XMLSCHEMA,
         '//xsd:element[@type="TReference-20C-01"]',
         '@xdb:SQLInline',
         'false',
         'xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsd="http://www.w3.org/2001/XMLSchema"'
       )
        into V_XMLSCHEMA
        from dual;
    
      select insertChildXML
       (
         V_XMLSCHEMA,
         '//xsd:element[@type="TParty-95PQR-01"]',
         '@xdb:SQLInline',
         'false',
         'xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsd="http://www.w3.org/2001/XMLSchema"'
       )
        into V_XMLSCHEMA
        from dual;
      --
      dbms_xmlschema_annotate.setOutOfLine(V_XMLSCHEMA,'element','ReceiveFreeConfirmationType','TwoLegTransactionDetails','MT544_TWO_LEG_TRANS_TABLE');
      dbms_xmlschema_annotate.setOutOfLine(V_XMLSCHEMA,'element','ReceiveFreeConfirmationType','TradeDetails','MT544_TRADE_DETAILS_TABLE');
      dbms_xmlschema_annotate.setOutOfLine(V_XMLSCHEMA,'complexType','TTradeDetails','FinancialInstrumentAttributes','MT544_FIN_INST_ATTRS_TABLE');
      --
      -- -----------------------------------------------
      -- DEBUG generated xdb annotations via resource
      -- -----------------------------------------------
      if (dbms_xdb.existsResource('/public/'||V_FILENAME)) then
          dbms_xdb.deleteResource('/public/'||V_FILENAME);
      end if;
      res := dbms_xdb.createResource('/public/'||V_FILENAME,V_XMLSCHEMA);
      -- -----------------------------------------------
      --
      dbms_xmlschema.registerSchema
      (
       schemaurl        => V_SCHEMA_LOC||V_FILENAME,
        schemadoc       => V_XMLSCHEMA.getClobVal(),
        local           => TRUE,
        genTypes        => TRUE,
        genBean         => FALSE,
        genTables       => TRUE,
        enablehierarchy => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE,
        owner           => user
      );
    end;
    /
    --
    -- ALTER session SET events = '31098 trace name errorstack off';
    Edited by: Marco Gralike on Jan 29, 2013 1:00 PM
  • 10. Re: How to register multiple XSD files
    eoin62 Newbie
    Currently Being Moderated
    @mdrake

    Hi Mark.

    I work with beta32c and can upload the XSD files to support, if that works. Thanks for the help.
  • 11. Re: How to register multiple XSD files
    mdrake Expert
    Currently Being Moderated
    Please do so and either email me or pos the SR number when done.
  • 12. Re: How to register multiple XSD files
    eoin62 Newbie
    Currently Being Moderated
    The Create SR button isn't working on my support account, so I sent the file location to mdrake via My Oracle Support Community - Private Messages. Hope that worked ; )

    Thanks!
  • 13. Re: How to register multiple XSD files
    mdrake Expert
    Currently Being Moderated
    Send me email at MARK DOT DRAKE AT ORACLE DOT COM...
  • 14. Re: How to register multiple XSD files
    eoin62 Newbie
    Currently Being Moderated
    Ok, has been sent.
1 2 Previous Next

Legend

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