2 Replies Latest reply: Jun 27, 2012 9:04 AM by 784111 RSS

    Difference in registering versus not registering an XSD with xml files

    784111
      SQL> select * from v$version ;

      BANNER

      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      PL/SQL Release 11.1.0.7.0 - Production
      CORE 11.1.0.7.0 Production
      TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
      NLSRTL Version 11.1.0.7.0 - Production

      --------------------------------------------------------------------------------

      With the help of members of this forum, I have been able to successfully register an XSD, save xml files into tables with xmltype columns, and select data from the xml files. For this I am very grateful.

      Forgive me not knowing this but, I can currently do these two things.

      1. Register an XSD and create a table with an xmltype column based off of this XSD. Insert xml into this table that matches the format of the xsd.
      2. Insert an xml document into a table with an xmltype column without registering it's corresponding XSD and then select data from this table too.

      Is there a performance benefit to registering the XSD the xml file is based off of, when it comes to inserting into the table or selecting data from the tables after the insert ?

      The xml files we insert could be anywhere from 1MB to beyond 500MB+ in size, as it depends on the amount of audit data generated. Is there a sweet spot as to how big or small you would keep the size of the xml files to avoid performance issues. If we need to do an audit in the future we would be inserting all the generated xml files into a table and run selects on that table. This data could be anywhere from a day's worth to everything from the time the system went live.

      We want to avoid any over head associated with XSDs if they do hinder performance.

      Thanks for your help in advance.

      Wally
        • 1. Re: Difference in registering versus not registering an XSD with xml files
          odie_63
          The xml files we insert could be anywhere from 1MB to beyond 500MB+ in size, as it depends on the amount of audit data generated.
          Schema-based XMLType (aka Object-Relational or structure storage) is exactly what you need in this case.

          - When you register a schema within the database, Oracle automatically creates a set of object types, collections and tables that map the XML structure to the SQL data model.
          - When you insert an XML instance document in the schema-based table, the XML content is then stored in the underlying relational structure.
          - When you issue queries against the XMLType table, for example using XQuery and XMLTable, Oracle automatically rewrites the query to access the relational objects where the data resides. This process results in very efficient queries whose performance compares to queries against regular tables. The Object-Relational structure also supports indexing.

          The documentation has whole chapters about it :
          http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb05sto.htm#g1070409
          http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb_rewrite.htm#BABHJECG
          http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb06stt.htm#BABGFCFG
          • 2. Re: Difference in registering versus not registering an XSD with xml files
            784111
            Thanks for the response Odie.

            We'll start using the XSD going forward.