4 Replies Latest reply: Jun 19, 2011 12:36 PM by mh*379860*ly RSS

    compex type restriction problem

    mh*379860*ly
      HI Gentlemen,

      I have a schema, ehd_root.xsd from which many different schemas are derived, mainly by restriction. Here is a complex type, ehd_body_typ, which only has a placeholder element.
           <xs:complexType name="ehd_body_typ">
                <xs:choice minOccurs="0" maxOccurs="unbounded">
                     *<xs:any processContents="skip" minOccurs="0" maxOccurs="unbounded"/>*
                </xs:choice>
           </xs:complexType>
      Now there is another schema, go_root.xsd with a complex type go_body_typ as
           <xs:complexType name="go_body_typ">
                <xs:complexContent>
                     <xs:restriction base="ehd_body_typ">
                          <xs:sequence>
                               *<xs:element ref="go:gnr_liste" maxOccurs="unbounded"/>*
                          </xs:sequence>
                     </xs:restriction>
                </xs:complexContent>
           </xs:complexType>
      Clearly, the <any> element should be substituted by the contents of go_body_typ. The problem is that Oracle does not support complex_type inheritance by restriction. "Because inheritance support in SQL does not support a notion of restriction, the SQL type corresponding to the restricted complexType is a empty subtype of the parent object type" (taken from Oracle® XML DB Developer's Guide, chapter 8 XML Schema Storage and Query: Advanced). This means that <any> will be taken from the supertype as varchar2(4000) which is extremely short for me. I badly need object-relational storage, so I can not rely on CLOBs which would be a solution.

      Well, if I copy the core element
                               *<xs:element ref="go:gnr_liste" maxOccurs="unbounded"/>*
      from go_root.xsd into ehd_root.xsd it works fine, but what if another complex type, krw_body_type would like to restrict the original ehd_body_type? The two subtypes are quite different: I can not bind the supertype to just one single subtype. I must preserve its generality.

      Can anyone give me an idea?

      Thanks, kind regards:

      Miklos HERBOLY
        • 1. Re: compex type restriction problem
          odie_63
          Hi Miklos,

          You may want to ask in the {forum:id=34} forum instead.
          As you pointed out, the documentation seems clear about your issue, but maybe you'll get alternatives there.

          For what usage do you need Object-Relational storage specifically? Query performance, storage optimization?

          Maybe you could be interested in BINARY XML storage as well, available starting with 11.1.
          It's now become the default storage option for XMLType columns in the database.

          As well as OR storage, the Binary XMLType may be schema-based, and it's designed - among other features - for optimized access of the data through XQuery.
          • 2. Re: compex type restriction problem
            mh*379860*ly
            HI odie,

            Thank you for your contribution! For the most part I am interested in object-relational storage because of performance. Nevertheless, I never compared the two models' behavior--documentation says that OR is top. And it accepts SQL annotations, so when registering anew, I get the same typenames.

            Another problem which I experienced: I successfully registered the schemas as binary, created the table with XMLTYPE column (this time it must be partitioned by list to accommodate 18 huge XMLTYPES in parallel, so I added an "id" column.)

            And when I want to insert just an integer (id) and one big xml instance, I get LSX-00333: literal ~S is not valid with respect to the pattern. And only with binary storage model. CLOB and OR work perfectly well. Do you have any idea why binary dislikes it so much? I have been investigating in both forums, in My Oracle Support, but nothing applies. Maybe I directly ran into a bug again?

            By the way, I posted the SR upon your suggestion in XML DB, no response until now.

            Please let me know if you have something.

            Thanks, kind regards

            Miklos
            • 3. Re: compex type restriction problem
              odie_63
              And when I want to insert just an integer (id) and one big xml instance, I get LSX-00333: literal ~S is not valid with respect to the pattern. And only with binary storage model. CLOB and OR work perfectly well. Do you have any idea why binary dislikes it so much?
              I don't know.
              Could it be possible you post the whole schema and an instance document?
              • 4. Re: compex type restriction problem
                mh*379860*ly
                With pleasure!
                But it consists of many thousends of lines of xml code and as far as I know there is no possibility to upload files. Can you suggest some mail address or ftp server?

                At most, I can give you the backbone of the story, hope it helps.

                My database version is 11.2.0.1.0, Enterprise Edition.

                (1) Creating a table with one scalar (partition key) and an xml document column:
                drop table ebm
                /
                CREATE TABLE ebm (
                  kv                CHAR(2), 
                  xml_document           XMLType)
                  XMLTYPE COLUMN xml_document STORE AS BINARY XML
                  XMLSCHEMA           "go_root_V1.30.xsd"
                  ELEMENT           "ehd"
                  PARTITION BY LIST (kv)
                    (
                    PARTITION kv_Schleswig_Holstein VALUES ('01'),
                    PARTITION kv_Hamburg VALUES ('02'),
                    PARTITION kv_Bremen VALUES ('03'),
                    PARTITION kv_Niedersachsen VALUES ('17'),
                    PARTITION kv_Westfalen_Lippe VALUES ('20'),
                    PARTITION kv_Nordrhein VALUES ('38'),
                    PARTITION kv_Hessen VALUES ('46'),
                    PARTITION kv_Rheinland_Pfalz VALUES ('51'),
                    PARTITION kv_Baden_Wuerttemberg VALUES ('52'),
                    PARTITION kv_Bayerns VALUES ('71'),
                    PARTITION kv_Berlin VALUES ('72'),
                    PARTITION kv_Saarland VALUES ('73'),
                    PARTITION kbv VALUES ('74'),
                    PARTITION kv_Mecklenburg_Vorpommern VALUES ('78'),
                    PARTITION kv_Brandenburg VALUES ('83'),
                    PARTITION kv_Sachsen_Anhalt VALUES ('88'),
                    PARTITION kv_Thueringen VALUES ('93'),
                    PARTITION kv_Sachsen VALUES ('98')
                    )
                /
                (2) Trying to insert into it with the following sql script:
                /* loadXmlFile_INT.sql - interactive version */
                
                declare
                  InstanceDocument varchar2(4000);
                begin
                  INSERT INTO &XMLTypeTable
                    VALUES (XMLType(bfilename('SOURCE_DIR', '&InstanceDocument'),
                            nls_charset_id('ISO-8859-1')));
                end;
                /
                (3) Running the script:
                SQL> @loadxmlfileascolumn_int
                Geben Sie einen Wert für xmltypetable ein: ebm
                alt   4:   INSERT INTO &XMLTypeTable
                neu   4:   INSERT INTO ebm
                Geben Sie einen Wert für id ein: 46
                Geben Sie einen Wert für instancedocument ein: 851_01.30_46_tf2009q3_nr1_prf.xml
                alt   5:     VALUES (&id, XMLType(bfilename('SOURCE_DIR', '&InstanceDocument'),
                neu   5:     VALUES (46, XMLType(bfilename('SOURCE_DIR', '851_01.30_46_tf2009q3_nr1_prf.xml'),
                declare
                *
                FEHLER in Zeile 1:
                ORA-31061: XDB-Fehler: XML event error 
                ORA-19202: Fehler bei XML-Verarbeitung  
                LSX-00333: Message 333 not found; No message file for product=XDK, facility=LSX 
                aufgetreten 
                ORA-06512: in Zeile 4 
                (4)
                Error messages documentation says:
                LSX-00333: literal "~S" is not valid with respect to the pattern
                    Cause: Literal is not valid with respect to the pattern.
                    Action: Choose correct characters in the literal.
                Kind regards,
                Miklos

                Edited by: mh**** on Jun 19, 2011 5:34 AM