2 Replies Latest reply on Aug 31, 2013 8:17 AM by 991278

    Query about XMLTYPE column structured storage in Oracle Xml db


      Dear All,


      DB Version: Oracle 11g (


      I have an table having one column as XMLTYPE with Structured storage.


      CREATE TABLE Orders
          Order_id NUMBER NOT NULL,
          Order_etc VARCHAR2(100),
          Order_desc XMLType NOT NULL
          XMLTYPE Order_desc STORE AS OBJECT RELATIONAL XMLSCHEMA  "http://localhost/public/xsd/order_desc_xsd.xsd" ELEMENT "OrderState";


      I have then registered the XSD with XML Db schema which is required for Structured storage.

      Before this table creation I had created a table (db_objects) of XMLTYPE and was able to use the below query to check for what all objects the XMLTYPE table got broken into when I registered its XSD.


          SELECT column_name,      
          FROM   user_tab_cols
          WHERE  table_name = 'DB_OBJECTS';


      And used below query to look for data stored in Object-Relational structure for my table (DB_OBJECTS) created with XMLTYPE definition.


        SELECT EXTRACTVALUE(xseq.column_value, '/THISROW/OWNER')       AS owner
          ,      EXTRACTVALUE(xseq.column_value, '/THISROW/OBJECT_NAME') AS object_name
          ,      EXTRACTVALUE(xseq.column_value, '/THISROW/OBJECT_TYPE') AS object_type
          ,      EXTRACTVALUE(xseq.column_value, '/THISROW/OBJECT_ID')   AS object_id
          ,      EXTRACTVALUE(xseq.column_value, '/THISROW/CREATED')     AS created
          FROM   db_objects do
           ,      TABLE(XMLSEQUENCE(EXTRACT(VALUE(do), '/ROWSET/THISROW'))) xseq  
          WHERE  ROWNUM <= 10;


      Now could someone let me know, how to find how the column (Order_desc) of XMLTYPE was broken down into further objects just like I did for the Table with XMLTYPE (as shown above)?


      Many Thanks.

        • 1. Re: Query about XMLTYPE column structured storage in Oracle Xml db

          First given that you are on 11.2, ExtractValue is deprecated and the documentation lists three options to use instead.  Here is one option (untested)

          SELECT owner, object_name, object_type, object_id, created
            FROM db_objects do,
                          PASSING do.object_value
                          -- Set data types accordingly
                          owner        VARCHAR2(20) PATH 'owner', 
                          object_name  VARCHAR2(20) PATH 'object_name', 
                          object_type  VARCHAR2(20) PATH 'object_type', 
                          object_id    VARCHAR2(20) PATH 'object_id', 
                          created      VARCHAR2(20) PATH 'created');


          Second, why does column order matter?  You are storing in an object relational method.  As long as the XML is valid per the schema, the Oracle will be able to store the data and later retrieve it as well.  How that data is stored is mostly Oracle internals and should not be touched as it can be changed from version to version.  You can use schema annotation to control how Oracle maps and stores the XML, but nothing in there specifies column order that I am aware of.


          It seems additional details are missing as to what you need the information for so that would help others answer your question.

          • 2. Re: Query about XMLTYPE column structured storage in Oracle Xml db

            Thanks...got the underlying structure.