7 Replies Latest reply: Dec 10, 2013 5:56 PM by Marco Gralike RSS

    Registering XSD creates generated table

    978225

      Hello,

       

      I am registering an XSL schema in oracle 11.2.0.2:

      begin
      DBMS_XMLSCHEMA.REGISTERSCHEMA(
      'http://mydomain.com/test.xsd',
      '<?xml version="1.0" encoding="UTF-8"?>
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:storeVarrayAsTable="true" oraxdb:schemaURL="http://mydomain.com/test.xsd">
        <xs:element name="restRoot" oraxdb:SQLName="TEST_ROOT" oraxdb:SQLType="TEST_ROOT_TYPE"  oraxdb:defaultTable="T_XSL_TEST_ROOT">
          <xs:complexType oraxdb:SQLType="TEST_ROOT_TYPE">
            <xs:sequence>
              <xs:element maxOccurs="unbounded" ref="testL2" oraxdb:SQLName="TEST_L2" oraxdb:SQLType="TEST_L2_TYPE"   oraxdb:SQLCollType="TEST_L2_COLL" oraxdb:defaultTable="T_XSL_TEST_L2_COLL"/>
              <xs:element ref="testRootId" oraxdb:SQLName="TEST_ROOT_ID" oraxdb:SQLType="VARCHAR2" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="testL2" oraxdb:SQLName="TEST_L2" oraxdb:SQLType="TEST_L2_TYPE"  oraxdb:defaultTable="T_TEST_L2">
          <xs:complexType oraxdb:SQLType="TEST_L2_TYPE">
            <xs:sequence>
              <xs:element ref="testL2Id" oraxdb:SQLName="L2_ID" oraxdb:SQLType="NUMBER"  />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="testL2Id" type="xs:integer" oraxdb:SQLName="L2_ID" oraxdb:SQLType="NUMBER"  oraxdb:defaultTable="T_XSL_TEST_L2_ID"/>
        <xs:element name="testRootId" type="xs:string" oraxdb:SQLName="ROOT_ID" oraxdb:SQLType="VARCHAR2"  oraxdb:defaultTable="T_XSL_TEST_ROOT_ID"/>
      </xs:schema>');
      END;
      /
      

      This creates the various tables I have specified, plus one more called "SYS_NT7NplvrDZBHDgQDKgbZMzFQ==".

      Where does that table come from, and how can I set a different name for it?

      I assume it comes from the complexType (collection?) for TEST_L2_TYPE, and I tried setting a oraxdb:defaultTable on that one too, but it did not like that...

       

      In SqlDeveloper, when I pull up the table and click the details tab, it says among other things:

      OBJECT_ID_TYPESYSTEM GENERATED
      TABLE_TYPETEST_L2_TYPE

       


      When I try to describe it in sqlplus, I get this:

       

      SQL> describe "SYS_NT7NplvrDZBHDgQDKgbZMzFQ==";

      Name                                      Null?    Type

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

      SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T

      L2_ID                                              NUMBER(38)

        • 1. Re: Registering XSD creates generated table
          odie_63

          Hi,

           

          This creates the various tables I have specified, plus one more called "SYS_NT7NplvrDZBHDgQDKgbZMzFQ==".

          Where does that table come from, and how can I set a different name for it?

          I assume it comes from the complexType (collection?) for TEST_L2_TYPE, and I tried setting a oraxdb:defaultTable on that one too, but it did not like that...

          Among all those tables you've specified, the only one you need is the root table ("T_XSL_TEST_ROOT").

          You should use oraxdb:defaultTable="" for the others, in order to prevent their creations.

           

          You're assuming correctly about the system generated table, it's been created to hold the data from the testL2 element (since it's declared with maxOccurs="unbounded").

          You can find its metadata by querying the following dictionary views :

          select * from user_nested_tables;

          select * from user_nested_table_cols;

          You can rename it with a simple ALTER TABLE RENAME, or using the DBMS_XMLSTORAGE_MANAGE.renameCollectionTable procedure which also takes care of renaming related indexes :

           

          SQL> begin

            2    DBMS_XMLSCHEMA.REGISTERSCHEMA(

            3    'http://mydomain.com/test.xsd',

            4    '<?xml version="1.0" encoding="UTF-8"?>

            5    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" xmlns:oraxdb="http://xmlns.oracle.com/xdb">

            6      <xs:element name="testRoot" oraxdb:SQLName="TEST_ROOT" oraxdb:SQLType="TEST_ROOT_TYPE" oraxdb:defaultTable="T_XSL_TEST_ROOT">

            7        <xs:complexType oraxdb:SQLType="TEST_ROOT_TYPE">

            8          <xs:sequence>

            9            <xs:element maxOccurs="unbounded" ref="testL2" oraxdb:SQLName="TEST_L2" oraxdb:SQLType="TEST_L2_TYPE" oraxdb:SQLCollType="TEST_L2_COLL" />

          10            <xs:element ref="testRootId" oraxdb:SQLName="TEST_ROOT_ID" oraxdb:SQLType="VARCHAR2" />

          11          </xs:sequence>

          12        </xs:complexType>

          13      </xs:element>

          14      <xs:element name="testL2" oraxdb:SQLName="TEST_L2" oraxdb:SQLType="TEST_L2_TYPE">

          15        <xs:complexType oraxdb:SQLType="TEST_L2_TYPE">

          16          <xs:sequence>

          17            <xs:element ref="testL2Id" oraxdb:SQLName="L2_ID" oraxdb:SQLType="NUMBER" />

          18          </xs:sequence>

          19        </xs:complexType>

          20      </xs:element>

          21      <xs:element name="testL2Id" type="xs:integer" oraxdb:SQLName="L2_ID" oraxdb:SQLType="NUMBER" />

          22      <xs:element name="testRootId" type="xs:string" oraxdb:SQLName="ROOT_ID" oraxdb:SQLType="VARCHAR2" />

          23    </xs:schema>');

          24  END;

          25  /

           

          PL/SQL procedure successfully completed

           

          SQL>

          SQL> select table_name, parent_table_name, parent_table_column

            2  from user_nested_tables;

           

          TABLE_NAME                       PARENT_TABLE_NAME   PARENT_TABLE_COLUMN

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

          SYS_NTPG5sNiL5RtGHHVm9Dfd2Ow==   T_XSL_TEST_ROOT     "XMLDATA"."TEST_L2"

           

          SQL> exec DBMS_XMLSTORAGE_MANAGE.renameCollectionTable(user, 'T_XSL_TEST_ROOT', null, '"XMLDATA"."TEST_L2"', 'T_TEST_L2_NT');

           

          PL/SQL procedure successfully completed

           

          SQL>

          SQL> select table_name, parent_table_name, parent_table_column

            2  from user_nested_tables;

           

          TABLE_NAME                       PARENT_TABLE_NAME   PARENT_TABLE_COLUMN

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

          T_TEST_L2_NT                     T_XSL_TEST_ROOT     "XMLDATA"."TEST_L2"

           

           

          The other option is to disable table creation at registration time, and manually create the root table just like you did in your previous thread :

          How to create index on XMLTYPE ordered collection table?

          There you can explicitly name the nested table.

          • 2. Re: Registering XSD creates generated table
            978225

            So... I think I have a handle on what is happening on the nested levels of the XML documents with the OCTs. However, I am still confused about the topmost root level.

             

            I supress the table generation using gentables=false in the XSL registration call, and I use a regular a parent table with a O-R XmlType column which stores the XML documents, rather than a full O-R XmlType table. Does the engine still generate an internal table somewhere for the outermost XML level (the root nodes)? If so, how can I find out what that table is called, and can I set the name myself? Or does the O-R storage nature with separate tables of the XmlType column really only apply to the inner nested levels of the XML documents from the 2nd level on; and the root nodes and all the singleton elements in the root level are really just stored in a column of the parent table?


            I did read the docs, but somehow my brain just refuses to fully grasp this part...

            • 3. Re: Registering XSD creates generated table
              odie_63

              Does the engine still generate an internal table somewhere for the outermost XML level (the root nodes)?

              No, only those you instruct Oracle to create via the CREATE TABLE command, i.e. the one storing the XML document and the associated nested tables to store collections (if any).

               

               

              Or does the O-R storage nature with separate tables of the XmlType column really only apply to the inner nested levels of the XML documents from the 2nd level on; and the root nodes and all the singleton elements in the root level are really just stored in a column of the parent table?

              Not really sure what you mean here.

              I think you're confusing top-level elements declared in the schema and which element is the root node (one XML document has only one root node).


              The defaultTable annotation controls the naming of XMLType tables generated to store element data declared top-level in the schema.

              But typically you almost always need to generate only one : the table storing the XML document, starting with the root node.

              Exception to that is when the schema defines recursive structures. There are some examples in the documentation too.


              And about using an XMLType table or an XMLType column in a regular heap table, there's no big difference regarding how to proceed.

              Storing in an XMLType object table is mandatory if you want to leverage XML DB repository functionalities such as automatic loading and shredding of XML documents.


              • 4. Re: Registering XSD creates generated table
                978225

                To clarify my question (hopefully ): I register this modified XSL (all defaultTable names set to blank and genTables set to false):

                 

                begin
                DBMS_XMLSCHEMA.REGISTERSCHEMA(
                'http://mydomain.com/test1.xsd',
                '<?xml version="1.0" encoding="UTF-8"?>
                <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:storeVarrayAsTable="true" oraxdb:schemaURL="http://mydomain.com/test1.xsd">
                  <xs:element name="testRoot" oraxdb:SQLName="TEST_ROOT" oraxdb:SQLType="TEST_ROOT_TYPE"  oraxdb:defaultTable="">
                    <xs:complexType oraxdb:SQLType="TEST_ROOT_TYPE">
                      <xs:sequence>
                        <xs:element maxOccurs="unbounded" ref="testL2" oraxdb:SQLName="TEST_L2" oraxdb:SQLType="TEST_L2_TYPE"   oraxdb:SQLCollType="TEST_L2_COLL" oraxdb:defaultTable=""/>
                        <xs:element ref="testRootId" oraxdb:SQLName="TEST_ROOT_ID" oraxdb:SQLType="VARCHAR2" />
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                  <xs:element name="testL2" oraxdb:SQLName="TEST_L2" oraxdb:SQLType="TEST_L2_TYPE"  oraxdb:defaultTable="">
                    <xs:complexType oraxdb:SQLType="TEST_L2_TYPE">
                      <xs:sequence>
                        <xs:element ref="testL2Id" oraxdb:SQLName="L2_ID" oraxdb:SQLType="NUMBER"  />
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                  <xs:element name="testL2Id" type="xs:integer" oraxdb:SQLName="L2_ID" oraxdb:SQLType="NUMBER"  oraxdb:defaultTable=""/>
                  <xs:element name="testRootId" type="xs:string" oraxdb:SQLName="ROOT_ID" oraxdb:SQLType="VARCHAR2"  oraxdb:defaultTable=""/>
                </xs:schema>',
                gentables=>false);
                END;
                /
                
                

                It does generate the types, but no tables - so far so good.

                 

                Then I create the table, specifying a OCT table for the testL2 nodes as T_TEST_L2_OCT:

                CREATE TABLE TEST_XML (
                  ID NUMBER(20) NOT NULL ENABLE,
                  ATT1 VARCHAR(10),
                  ATT2 VARCHAR(10),
                  XML XMLTYPE,
                  CONSTRAINT PK_TEST_XML PRIMARY KEY(ID)
                )
                VARRAY "XML"."XMLDATA"."TEST_L2" STORE AS TABLE "T_TEST_L2_OCT" (NOLOGGING) RETURN AS LOCATOR
                XMLTYPE XML STORE AS OBJECT RELATIONAL XMLSCHEMA "http://mydomain.com/test1.xsd" ELEMENT "testRoot";
                
                

                 

                 

                 

                And insert a document:

                INSERT INTO TEST_XML VALUES (1, 'a', 'b',
                XMLTYPE('
                <testRoot>
                  <testRootId>RID1</testRootId>
                  <testL2>
                    <testL2Id>1</testL2Id>
                  </testL2>
                  <testL2>
                    <testL2Id>2</testL2Id>
                  </testL2>
                  <testL2>
                    <testL2Id>3</testL2Id>
                  </testL2>
                </testRoot>')); 
                COMMIT;
                
                

                 

                By outermost root level data, I mean the testRootId in this example - or more generally attributes of the testRoot element or other singleton elements parallel to testRootId, if I had declared any. Where is that stored? In table TEST_XML, or somewhere else?

                 

                If I had specified a defaultTable name in the schema (let's just say "TEST_ROOT_TABLE") and set genTables to true, what would happen then? It would have created the table TEST_ROOT_TABLE, but would the inserted record be stored in TEST_ROOT_TABLE or in TEST_XML?

                If the data is being stored in TEST_XML, why would one ever specify a defaultTable name and have Oracle generate it, if it does not being used anyway?

                • 5. Re: Registering XSD creates generated table
                  odie_63

                  By outermost root level data, I mean the testRootId in this example - or more generally attributes of the testRoot element or other singleton elements parallel to testRootId, if I had declared any. Where is that stored? In table TEST_XML, or somewhere else?

                  Yes, it's stored in TEST_XML, as a hidden column :

                  SQL> select column_name, data_type, hidden_column, qualified_col_name

                    2  from user_tab_cols

                    3  where table_name = 'TEST_XML';

                   

                  COLUMN_NAME         DATA_TYPE        HIDDEN_COLUMN QUALIFIED_COL_NAME

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

                  ID                  NUMBER           NO            ID

                  ATT1                VARCHAR2         NO            ATT1

                  ATT2                VARCHAR2         NO            ATT2

                  XML                 XMLTYPE          NO            XML

                  SYS_NC00005$        XMLTYPEEXTRA     YES           SYS_NC00005$

                  SYS_NC00006$        XMLTYPEPI        YES           "SYS_NC00005$"."NAMESPACES"

                  SYS_NC00007$        XMLTYPEPI        YES           "SYS_NC00005$"."EXTRADATA"

                  SYS_NC00008$        TEST_ROOT_TYPE   YES           SYS_NC00008$

                  SYS_NC00009$        XDB$RAW_LIST_T   YES           "SYS_NC00008$"."SYS_XDBPD$"

                  SYS_NC00010$        TEST_L2_COLL     YES           "SYS_NC00008$"."TEST_L2"

                  SYS_NC0001000011$   RAW              YES           "SYS_NC00008$"."TEST_L2"

                  SYS_NC00012$        VARCHAR2         YES           "SYS_NC00008$"."TEST_ROOT_ID"

                   

                  12 rows selected

                   

                   

                   

                  If I had specified a defaultTable name in the schema (let's just say "TEST_ROOT_TABLE") and set genTables to true, what would happen then? It would have created the table TEST_ROOT_TABLE, but would the inserted record be stored in TEST_ROOT_TABLE or in TEST_XML?

                  If the data is being stored in TEST_XML, why would one ever specify a defaultTable name and have Oracle generate it, if it does not being used anyway?

                  Well, obviously, there's no point in creating both tables. Just choose one method.

                  Either use the defaultTable annotation (only where necessary) and genTables "true", or don't and create the table manually.

                  The latter allows you to control all the storage options, including nested tables, at creation time without having to get back to it later to rename objects or alter storage options.

                  Also note that it's not mandatory to declare the nested tables in the CREATE TABLE statement, Oracle will take care of generating the necessary objects based on the schema (default behaviour starting with 11.2), but of course it will use system-generated names.

                   

                  The default table mechanism is interesting when we want to work with the XML DB repository. It provides a way to load schema-based XML documents automatically when they're dropped in a repository folder.

                  • 6. Re: Registering XSD creates generated table
                    978225

                    That info from user_tab_cols was what was missing for me. Now I got it.

                     

                    Now just one last thing: what is the difference between those two TEST_L2 columns? Is one a reference and the other the raw XML subtree for that level of the XML document?

                     

                    COLUMN_NAME         DATA_TYPE        HIDDEN_COLUMN QUALIFIED_COL_NAME

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

                    SYS_NC00010$        TEST_L2_COLL     YES           "SYS_NC00008$"."TEST_L2"

                    SYS_NC0001000011$   RAW              YES           "SYS_NC00008$"."TEST_L2"

                    • 7. Re: Registering XSD creates generated table
                      Marco Gralike

                      Just as a side note...

                       

                      > The default table mechanism is interesting when we want to work with the XML DB repository. It provides a way to load schema-based XML documents automatically when they're dropped in a repository folder.

                       

                       

                      This is only because the default for "enablehierarchy" in DBMS_XMLSCHEMA.REGISTER_SCHEMA is ENABLE_HIERARCHY_CONTENTS in 11.2

                       

                      http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xmlsch.htm#ARPLS70078

                       

                       

                      You can disable this default behavior via ENABLE_HIERARCHY_NONE for "enablehierarchy"

                       

                      or

                       

                      ...manually enable hierarchy for manually created objects via package DBMS_XDBZ.ENABLE_HIERARCHY


                      http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xdbz.htm#ARPLS69481