2 Replies Latest reply: Oct 19, 2012 6:46 AM by user10863631 RSS

    Seeking advice on Best Practices for XML Storage Options - XMLTYPE

    user10863631
      Sparc64
      11.2.0.2

      During OOW12 I tried to attend every xml session I could. There was one where a Mr. Drake was explaining something about not using clob
      as an attribute to storing the xml and that "it will break your application."

      We're moving forward with storing the industry standard invoice in an xmltype column, but Im not concerned that our table definition is not what was advised:
      --i've dummied this down to protect company assets
      
        CREATE TABLE "INVOICE_DOC" 
         (     "INVOICE_ID" NUMBER NOT NULL ENABLE, 
           "DOC" "SYS"."XMLTYPE"  NOT NULL ENABLE, 
           "VERSION" VARCHAR2(256) NOT NULL ENABLE, 
           "STATUS" VARCHAR2(256), 
           "STATE" VARCHAR2(256), 
           "USER_ID" VARCHAR2(256), 
           "APP_ID" VARCHAR2(256), 
           "INSERT_TS" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
           "UPDATE_TS" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
            CONSTRAINT "FK_####_DOC_INV_ID" FOREIGN KEY ("INVOICE_ID")
                   REFERENCES "INVOICE_LO" ("INVOICE_ID") ENABLE
         ) SEGMENT CREATION IMMEDIATE 
      INITRANS 20   
      TABLESPACE "####_####_DATA" 
             XMLTYPE COLUMN "DOC" STORE AS BASICFILE CLOB  (
        TABLESPACE "####_####_DATA"  XMLTYPE COLUMN "DOC" STORE AS BASICFILE CLOB  (
        TABLESPACE "####_####_DATA" ENABLE STORAGE IN ROW CHUNK 16384 RETENTION 
        NOCACHE LOGGING 
        STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
       XMLSCHEMA "http://mycompanynamehere.com/xdb/Invoice###.xsd" ELEMENT "Invoice" ID #####"
      {code}
      
      What is a best practice for this type of table?  Yes, we intend on registering the schema against an xsd.
      
      Any help/advice would be appreciated.
      
      -abe                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        • 1. Re: Seeking advice on Best Practices for XML Storage Options - XMLTYPE
          odie_63
          Hi,

          I suggest you read this paper : Oracle XML DB : Choosing the Best XMLType Storage Option for Your Use Case

          It is available on the XML DB home page along with other documents you may be interested in.

          To sum up, the storage method you need depends on the requirement, i.e. how XML data is accessed.
          There was one where a Mr. Drake was explaining something about not using clob as an attribute to storing the xml and that "it will break your application."
          I think the message Mark Drake wanted to convey is that CLOB storage is now deprecated and shouldn't be used anymore (though still supported for backward compatibility).
          The default XMLType storage starting with version 11.2.0.2 is now Binary XML, a posted-parsed binary format that optimizes both storage size and data access (via XQuery), so you should at least use it instead of the BASICFILE CLOB.

          Schema-based Binary XML is also available, it adds another layer of "awareness" for Oracle to manage instance documents.
          To use this feature, the XML schema must be registered with "options => dbms_xmlschema.REGISTER_BINARYXML".

          The other common approach for schema-based XML is Object-Relational storage.


          BTW... you may want to post here next time, in the dedicated forum : {forum:id=34}
          Mark Drake is one of the regular user, along with Marco Gralike you've probably seen too at OOW.

          Edited by: odie_63 on 18 oct. 2012 21:55
          • 2. Re: Seeking advice on Best Practices for XML Storage Options - XMLTYPE
            user10863631
            Thank you, sir.

            Tremendous help; much appreciated.

            -abe