3 Replies Latest reply: Feb 19, 2013 6:37 AM by Marco Gralike RSS

    XML Schema <any> element

    beta32c
      I have a list of schema files and they a number of elements in them. But for my data extraction i use only close to 300 tags. When i register the schema I get an error that a table cannot of have more than 1000 columns. My work mate is working on Annotations and I am planning to build a new schema to make extract only these 300 tag names and also make sure the XML file can be validated like it did earlier. Explaining this i came across a complex type element called *<any>* which can be used to allow elements to be available which are not declared within the schema file.

      My Original schema file looks something like below,


      <xsd:complexType name="WfInformation">
      <xsd:all minOccurs="0">
      <xsd:element name="Company" type="WfCompany" minOccurs="0" />
      <xsd:element name="HeadOffice" type="WfOffice" minOccurs="0" />
      <xsd:element name="IndependentOffice" type="WfIOffice" minOccurs="0" />
      <xsd:element name="AffiliateCompany" type ="WfIAOffice" minOccurs="0" />
      <xsd:element name="Number" type="xsd:string" minOccurs="0" />
      <xsd:element name="ReferenceID" type="xsd:string" minOccurs="0" />
      </xsd:all>
      </xsd:complexType>

      of the above list of items i use only elements Company, HeadOffice, Number. After reading through some documentation I wanted to <any> tag like below,

      <xsd:complexType name="WfInformation">
      *<xsd:sequence>*
      <xsd:element name="Company" type="WfCompany" minOccurs="0" />
      <xsd:element name="HeadOffice" type="WfOffice" minOccurs="0" />
      *<xsd:any processContents="lax"/>*
      *<xsd:any processContents="lax"/>*
      <xsd:element name="Number" type="xsd:string" minOccurs="0" />
      *<xsd:any processContents="lax"/>*
      *</xsd:sequence>*
      </xsd:complexType>


      My question is if I am correcting the lines as i mentioned above will be Register schema again fail with same reason that i have 1000 columns. As i understand every element inside an Complex element is stored as a column value. In that case will this redesign work. Or is there any other way to approach re-desgin the XML Schema.
        • 1. Re: XML Schema <any> element
          mdrake
          AFAIK xsd:any cannot be used in conjuction will elements with minOccurs="0". So your complete type would need to be
               <xsd:complexType name="WfInformation">
                    <xsd:sequence>
                         <xsd:element name="Company" type="WfCompany" minOccurs="1"/>
                         <xsd:element name="HeadOffice" type="WfOffice" minOccurs="1"/>
                         <xsd:any processContents="lax"/>
                         <xsd:any processContents="lax"/>
                         <xsd:element name="Number" type="xsd:string" minOccurs="1"/>
                         <xsd:any processContents="lax"/>
                    </xsd:sequence>
               </xsd:complexType>
          Each any will be mapped to a VARCHAR2 or CLOB. This will not save you any columns in underlying table. Maybe if you had multiple elements following number and mapped them to any with maxOccurs unbounded this would work.

          That said the any will cause problems with optimizaiton of XPATH queries against the XML, and should be avoided at all costs.

          Can you send me the set of XMLSchemas and I'll run it through an internal tool that will identify a set of annotations that will avoid the 1000 column limiit.
          • 2. Re: XML Schema <any> element
            beta32c
            Thanks. I think that leaves out my option to create a totally new Schema to suit my XML files.
            Only other option we have is to use Annotations.

            I think my colleague - eoin62 would have shared the XML Schema files earlier through your oracle email address.
            Please refer thread - How to register multiple XSD files


            But we received a reply and we knew very little on how to make changes to Admin.xsd and how would that solve our problem. Please correct me if i am incorrect. Because the biggest Schema was estimate.xsd that has close to 800+ elements. I am not sure if my colleague sent you the necessary tag names we use within the Schema. We have close to around 300 tags we use. But our combined XML Schema has close to 1600+ If we have to annotate, i thought it would be better we tell Oracle which tagswe need, which inturn should should help better create XMLtype table against a Schema and work on performance.

            Your earlier reply was,

            Basically in XML Schema Admin.xsd element AdminCompin WorkfileTypeneeds to be stored Out of line..

            Add the annotations xdb:SQLInline=”false” and xdb:defaultTable=”ADMIN_COMP_XML”. to the element..


            if you can share with our XML Schema on how to annotate or if you can share a location that shows how an annotated or non-annotated Schema works that would help us correcting our Schema files.

            Thanks for all your support. We have gone a long way in XML Parsing in the last 6 weeks.

            Edited by: beta32c on Feb 19, 2013 12:09 AM
            • 3. Re: XML Schema <any> element
              Marco Gralike
              Besides the analysis tool, Mark and his team created the "XDB Utilities" set of PL/SQL packages to easily be able to programatically change and update the XML Schemas with annotations and other things.

              You can find and download the zip file (which also contains a "howto" pdf file) here:

              http://www.oracle.com/technetwork/indexes/samplecode/xmldb-sample-523617.html

              (Oracle XML DB Manageability Packages - xdb_util.zip)