This discussion is archived
2 Replies Latest reply: Apr 22, 2012 4:12 AM by 707232 RSS

how to create xmltype table from a schema without global element?

707232 Newbie
Currently Being Moderated
Hi there,

I'm wondering how to create a xmltype table from a schema A without its own global element declaration, while it 'import's another schema B which do has global element declared? It seems Oracle won't create a default table for A as it doesn't have a global element explicitly declared... (my schema A extends a ComplexType from schema B and that's its only purpose)? How to save a XML file comforming to A into the XMLDB database?

Many Thanks!

My schema B is actually named as 'EnvelopeDemo.xsd' which declared global element as below:
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" xmlns="my.demo.Envelope" targetNamespace="my.demo.Envelope" attributeFormDefault="unqualified">
     <xs:complexType name="EnvelopeType">
          <xs:sequence>
               <xs:element name="Data" type="DataItemBaseType"/>
          </xs:sequence>
     </xs:complexType>
     <xs:complexType name="DataItemBaseType">
     </xs:complexType>
     <xs:element name="Envelope" type="EnvelopeType"/>
</xs:schema>
And the primary schema A (ConcreteDataItemDemo.xsd) I want to use to validate my xml file is:
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="my.demo.ConcretDataItem" xmlns:env="my.demo.Envelope" targetNamespace="my.demo.ConcretDataItem" elementFormDefault="qualified" attributeFormDefault="unqualified">

     <xs:import namespace="my.demo.Envelope" schemaLocation="EnvelopeDemo.xsd"/>
     
     <xs:complexType name="ConcretDataItemType">
          <xs:complexContent>
               <xs:extension base="env:DataItemBaseType">
                    <xs:sequence>
                         <xs:element name="ConcretDataItem" type="xs:string" minOccurs="0"/>
                    </xs:sequence>
               </xs:extension>
          </xs:complexContent>
     </xs:complexType>          
</xs:schema>
I registered the schema as below:
--register schema for creating tables
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'EnvelopeDemo.xsd',
    SCHEMADOC => XDBURIType('/home/oracle/Desktop/EnvelopeDemo.xsd').getCLOB(),
    LOCAL     => True,
    GENTYPES  => FALSE, 
    GENTABLES => TRUE,
    ENABLEHIERARCHY  => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE,
    OPTIONS => DBMS_XMLSCHEMA.REGISTER_BINARYXML);
END;
/

--register schema for creating tables
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'ConcreteDataItemDemo.xsd',
    SCHEMADOC => XDBURIType('/home/oracle/Desktop/ConcreteDataItemDemo.xsd').getCLOB(),
    LOCAL     => True,
    GENTYPES  => FALSE, 
    GENTABLES => FALSE,
    ENABLEHIERARCHY  => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE,
    OPTIONS => DBMS_XMLSCHEMA.REGISTER_BINARYXML);
END;
/
When I set the GENTABLES=>TRUE for the registration of ConcreteDataItemDemo.xsd, Oracle didn't create a default table automatically. Then I tried to register it with GENTABLES => FALSE, hoping to create a table manually by referencing that schema, but I can't do it as I don't know which root element to specify in the CREATE TABLE statement.

I tried to use the global element in schema B i.e. EnvelopeDemo.xsd by specifying 'ELEMENT "env:Envelope"' but railed:
create table dataitem of XMLTYPE
XMLSCHEMA "ConcreteDataItemDemo.xsd"
ELEMENT "env:Envelope";
/

create table dataitem of XMLTYPE
XMLSCHEMA "ConcreteDataItemDemo.xsd"
ELEMENT "env:Envelope"
Error at Command Line:37 Column:0
Error report:
SQL Error: ORA-31043: Element 'env:Envelope' not globally defined in schema 'ConcreteDataItemDemo.xsd'
31043. 00000 -  "Element '%s' not globally defined in schema '%s'"
*Cause:    The specified element name has not been defined at the top level
           of the XML schema (i.e. globally).  Elements must be defined
           globally to be the root of an XMLType object.
*Action:   Check the XML schema definition to make sure the specified
           element name has been defined at the top level.
=============================
SQL> select * from V$VERSION
2 ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Edited by: Johnny Chen on 2012-4-22 上午12:30

Edited by: Johnny Chen on 2012-4-22 上午12:31

Edited by: Johnny Chen on 2012-4-22 上午4:18
  • 1. Re: how to create xmltype table from a schema without global element?
    odie_63 Guru
    Currently Being Moderated
    Hi Johnny,

    Could you also post a sample instance document that's supposed to be validated against this configuration?
  • 2. Re: how to create xmltype table from a schema without global element?
    707232 Newbie
    Currently Being Moderated
    Hi odie_63,

    Thanks for looking into this, here is an instance xml file supposed to be valid against the ConcreteDataItemDemo.xsd. The EnvelopeDemo.xsd is supposed to be a generic envelope wrapper, and the ConcreteDataItemDemo.xsd is supposed to define the concrete content structure for real data.

    Cheers,
    Johnny
    <?xml version="1.0" encoding="UTF-8"?>
    <!--Sample XML file generated by XMLSpy v2010 (http://www.altova.com)-->
    <env:Envelope xsi:schemaLocation="my.demo.ConcretDataItem ConcreteDataItemDemo.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:env="my.demo.Envelope" xmlns="my.demo.ConcretDataItem">
         <env:Data xsi:type="ConcretDataItemType">
              <ConcretDataItem>
                   "testdata in string"
              </ConcretDataItem>
         </env:Data>
    </env:Envelope>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points