1 2 Previous Next 16 Replies Latest reply: Dec 23, 2013 6:26 AM by user594708 RSS

    Register XML schema using dbms_xmlschema in Oracle 11g

    user594708


      I have a xsd file located at C:\temp\test.xsd

      I need to register this schema to create a XMLType table for Object Relational XML Storage.

      How do I register the schema for relational storage?

       

      I do not seem to find any example on internet for registering the schema for relational storage.

       

      Any help would be highly appreciated.

        • 1. Re: Register XML schema using dbms_xmlschema in Oracle 11g
          odie_63

          I do not seem to find any example on internet for registering the schema for relational storage.

          You've got to be kidding

           

          XML Schema Storage and Query: Basic

           

          And you're gonna need to move the schema file to a location the db has access to (such as an Oracle DIRECTORY location).

          • 2. Re: Register XML schema using dbms_xmlschema in Oracle 11g
            user594708

            The example on the Oracle link is using bfile.

             

            BEGIN

              DBMS_XMLSCHEMA.registerSchema(

                SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',

                SCHEMADOC => bfilename('XMLDIR','purchaseOrder.xsd'),

                CSID      => nls_charset_id('AL32UTF8'));

            END;

             

            I want to register the schema for relational storage. This is because I want to create a XMLType table and store it as relational storage as shown below.

             

            CREATE TABLE XMLOR OF XMLType

            XMLTYPE store AS OBJECT RELATIONAL

            XMLSCHEMA "http://localhost/myXMLSchema.xsd" ELEMENT "Root";

             

            Also, can you please tell me what is the exact Oracle DIRECTORY location where the schema is to be placed.

             

            Sorry if the qurestions sound too silly; I am new to this area.

            • 3. Re: Register XML schema using dbms_xmlschema in Oracle 11g
              marc980813

              -- as sys

              CREATE DIRECTORY XML_DIR AS '/home/oracle/upload/install/xmldoc';

              GRANT READ ON DIRECTORY XML_DIR to public;

               

              - log as schema owner and if XML_DIR is there

              select * from all_directories;

               

              -- you can directly mention the name of the table to be created in the xsd,as well as the tablespace used and the primary key

              example of xsd first 2 lines

              <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" xdb:storeVarrayAsTable="true" xdb:schemaURL="STX_DEPT.XSD">

                  <xs:element name="Department" type="DepartmentType" xdb:maintainDOM="false" xdb:defaultTable="STX_DEPARTMENTS"

                                   xdb:tableProps="tablespace users" xdb:columnProps="CONSTRAINT dept_pkey PRIMARY KEY (XMLDATA.dname)" xdb:SQLName="Department">

               

              I would also give a name to the object types and the collection, example  :

              <xs:complexType name="EmployeesType" xdb:SQLType="STX_EMPLOYEES_T">

                      <xs:sequence>

                          <xs:element name="Employee" type="EmployeeType" minOccurs="0" maxOccurs="100" xdb:SQLName="EMPLOYEE" xdb:SQLCollType="STX_EMPLOYEES_C" xdb:SQLInline="true" xdb:maintainOrder="false"/>

                      </xs:sequence>

                  </xs:complexType>

               

              -- then register the schema

              BEGIN

                 DBMS_XMLSCHEMA.registerSchema(

                  SCHEMAURL=> 'STX_DEPT.XSD',

                  SCHEMADOC=> bfilename('XML_DIR','schemas/STX_DEPT.xsd'),

                  LOCAL=> TRUE,

                  GENTYPES=> TRUE,

                  GENTABLES=> TRUE,

                  CSID=> nls_charset_id('AL32UTF8'));

              END;

              /

              • 4. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                user594708


                I was able to register the schema successfully.

                 

                But when I try to create table of XMLType for the registered schema, it throws error.

                 

                CREATE TABLE myTab OF XMLType

                  XMLTYPE store AS OBJECT RELATIONAL

                  XMLSCHEMA "D:\ora\product\11.2.0\dbhome_1\install\DEPT.xsd" ELEMENT "Root";

                 

                 

                Error report:

                SQL Error: ORA-31000: Resource 'D:\ora\product\11.2.0\dbhome_1\install\DEPT.xsd' is not an XDB schema document

                31000. 00000 -  "Resource '%s' is not an XDB schema document"

                *Cause:    The given schema URL does not refer to a registered XDB schema

                *Action:   Make sure the specified schema has been registered in XDB

                • 5. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                  marc980813

                  in my example  SCHEMADOC=> bfilename('XML_DIR','schemas/STX_DEPT.xsd'), I saved the document in a subdirectory schemas

                  --> yours should look like this D:\XML_DB\XML_DIR\schemas\STX_DEPT.xsd

                  • 6. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                    user594708

                    Please see my post #4

                    I was able to register the schema but now getting error while creating table.

                     

                    My schema url is shown as -

                     

                    select schema_url from user_xml_schemas;

                     

                    SCHEMA_URL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

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

                    DEPT.XSD             

                    • 7. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                      odie_63

                      Sorry if the qurestions sound too silly; I am new to this area.

                       

                      If you're new then read the manual.

                      There's everything you need in there, just take some time to read it.

                       

                      And it's also easy to see that DBMS_XMLSCHEMA comes with different overloads that you can use depending on the source of the schema (BLOB, CLOB, BFILE, VARCHAR2, XMLType, Uri).

                      • 8. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                        odie_63
                        I was able to register the schema but now getting error while creating table.

                         

                        My schema url is shown as -

                         

                        select schema_url from user_xml_schemas;

                         

                        SCHEMA_URL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

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

                        DEPT.XSD           

                        Then that's the url you have to use in the CREATE TABLE statement :

                        CREATE TABLE myTab OF XMLType

                          XMLTYPE store AS OBJECT RELATIONAL

                          XMLSCHEMA "DEPT.XSD" ELEMENT "Root";

                        What exact command did you use to register the schema?

                        If you've passed genTypes = true and genTables = true then Oracle has already created a default schema-based XMLType table (with a system-generated name if no annotation was provided).

                        You may want to delete the schema and register again with genTables set to "false" if you want to create the table manually afterwards.

                        Or you may just keep the system-generated one and rename it to your liking.

                        You can find it by querying USER_XML_TABLES.

                         

                        Message was edited by: odie_63 Corrected typo in XMLSCHEMA clause

                        • 9. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                          user594708

                          I registered a new schema with genTypes = false and genTables = false and it did not create any tables while registering the schema.

                           

                          BEGIN

                             DBMS_XMLSCHEMA.registerSchema(

                              SCHEMAURL=> 'MYDEPT.XSD',

                              SCHEMADOC=> bfilename('XML_DIR','MYDEPT.xsd'),

                              LOCAL=> TRUE,

                              GENTYPES=> FALSE,

                              GENTABLES=> FALSE,

                              CSID=> nls_charset_id('AL32UTF8'));

                          END;

                           

                          select schema_url from user_xml_schemas;

                          SCHEMA_URL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

                          ----------------------------DEPT.XSD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

                          MYDEPT.XSD         

                           

                          But still the error ---

                           

                          CREATE TABLE TCOBJ OF XMLType

                            XMLTYPE store AS OBJECT RELATIONAL

                            XMLSCHEMA "MYDEPT.xsd" ELEMENT "Root";

                           

                          SQL Error: ORA-31000: Resource 'MYDEPT.xsd' is not an XDB schema document

                          31000. 00000 -  "Resource '%s' is not an XDB schema document"

                          *Cause:    The given schema URL does not refer to a registered XDB schema

                          *Action:   Make sure the specified schema has been registered in XDB

                          • 10. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                            odie_63

                            The schema URL is case-sensitive : you've registered it using 'MYDEPT.XSD' but using 'MYDEPT.xsd' in the CREATE TABLE statement.

                            (I've edited my previous post where I made the same mistake)

                             

                            You also have to set genTypes to "true", otherwise you won't benefit from Object Relational storage.

                             

                            BTW, what's your exact db version?

                            • 11. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                              user594708

                              Thanks for help. The table is created now. But it created a CLOB table whereas I want a relational table with rows and columns. Is it because the schema was registered with SCHEMADOC as bfilename? How do I get the relational storage?

                               

                              BEGIN
                                 DBMS_XMLSCHEMA.registerSchema(
                                  SCHEMAURL=> 'TESTDEPT.XSD',
                                  SCHEMADOC=> bfilename('XML_DIR','TESTDEPT.XSD'),
                                  LOCAL=> TRUE,
                                  GENTYPES=> TRUE,
                                  GENTABLES=> FALSE,
                                  CSID=> nls_charset_id('AL32UTF8'));
                              END;

                               

                              CREATE TABLE TESTDEPT OF XMLType
                                XMLTYPE store AS OBJECT RELATIONAL
                                XMLSCHEMA "TESTDEPT.XSD" ELEMENT "root";

                               

                              desc TESTDEPT;

                                Name                           Null     Type                                                                                                                                                                                         
                              ------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                              SYS_NC_ROWINFO$                         XMLTYPE()        

                               

                              SELECT dbms_metadata.get_ddl('TABLE','TESTDEPT') from dual;

                                DBMS_METADATA.GET_DDL('TABLE','TESTDEPT')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------(CLOB)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                CREATE TABLE "DMUSER"."TESTDEPT" OF XMLTYPE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                XMLSCHEMA "TESTDEPT.XSD" ELEMENT "root" ID 4090 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                TABLESPACE "USERS" 

                               

                              My database version:

                              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production    

                              PL/SQL Release 11.2.0.1.0 - Production                                          

                              CORE 11.2.0.1.0 Production                                                        

                              TNS for 64-bit Windows: Version 11.2.0.1.0 - Production                         

                              NLSRTL Version 11.2.0.1.0 - Production

                              • 12. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                                odie_63

                                But it created a CLOB table whereas I want a relational table with rows and columns.

                                No, it created an Object-Relational table, i.e. a table of the root object type created during registration, but wrapped as a virtual XMLType column.

                                You still haven't read the manual, have you?

                                 

                                That's the way it works.

                                If you want to create regular relational tables, then you'll have to do it manually.

                                Alternatively, you can simply build views over the XMLType table to access the data relationally.

                                • 13. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                                  user594708

                                  Thanks odie.

                                   

                                  My requirement is that after loading the xml data into object-relational table, I need to manipulate data in XMLType table through PL/SQL and then extract it back into an xml file.

                                   

                                  I read in Oracle's white paper - Oracle XML DB Choosing the best XMLType Storage Option for your Use Case - that XML can be used only as a staging area where producing relational values from XML as well as generating XML from relational data is covered under this category .

                                   

                                  I need the tables based on schema. So is building views over XMLType table the most appropriate way of doing this?


                                  • 14. Re: Register XML schema using dbms_xmlschema in Oracle 11g
                                    odie_63

                                    I need to manipulate data in XMLType table through PL/SQL and then extract it back into an xml file.

                                    What kind of manipulations? Could you develop a bit more? It would help finding solutions for your use case.

                                    Maybe you don't even need to load the file in the database if it's just to extract it back afterwards. What about XSLT ?

                                     

                                    If you can, please post a little test case relevant to your scenario so that we can suggest a viable solution.

                                    1 2 Previous Next