2 Replies Latest reply: Oct 23, 2012 7:40 PM by mdrake-Oracle RSS

    is it possible to remove XMLSchema restriction from an XMLType column?

      I have an XMLType column that is validated via some XMLSchema. Now I want to remove this restriction and make it just a generic XMLType column.
      I tried bunch of alter table commands but I couldn't figure out the magic combination.
      Is this possible?

      Here is the actual problem. Unfortunately, we ended up both local and global schemas using the same URL. We have customers
      out there with both schemas (early customers) or only global schema (new customers). Most of the tables were created before the global schema
      was added. So, they are referencing the local schema. Now we want to evolve our schema, and I am trying to write a sql script to clean-up this mess.

      My plan is:
      if only global schema exists then (these are late customers)
      - do nothing
      else if both global and local schema exist then (these are early customers)
      - If any table or table.column has dependency on local schema (I can find this out from user_dependencies) I am going to mark those
      tables/columns as no schema validated. i.e. remove all references to local schema
      - delete and purge the local schema
      - modify those tables/columns and make them point to the global schema
      end if
      - evolve the schema (only global one is left)

      Is there a better way/plan? Is there a way to modify a column definition and move its reference from local schema to global schema where
      both schemas have the same URL?
      Of course the main constraint is that we do not want to loose customer data. By the way, both local and global schemas are identical in terms of xsd.

      I guess a more generic question would be, is it possible to change XMLSchema/Element settings of an XMLType column from X to Y, X to null, or null to X? (Of course the assumption is Y is a superset of X.)
        • 1. Re: is it possible to remove XMLSchema restriction from an XMLType column?
          Marco Gralike
          I would ask for advise via a SR regarding the global/local issue, guessing you want the change to be done without a full rebuild of stuff.
          • 2. Re: is it possible to remove XMLSchema restriction from an XMLType column?
            Couple of Questions ?

            Which verson of the database...

            Are you using the XML DB repository to provide path based access to the XML Content.

            In general

            1. You cannot break the associated between a table and it's schema..

            So if I understand waht you want to do you will have to do something different..

            Copy the data into a non schema based table (Either XMLType Store AS CLOB or XMLType store as SecureFile Binary XML).
            YOu probably want to invoke the createNonSchemaBasedXML() operator when inserting the XML non schema based table.

            Once all the data is copied, delete and purge the local schema, and all of it's types and tables.

            Create a new table based on the Global Schema.

            Copy the database back into the new table..

            I know two data copies are a PITA, but I can think of no other way of doing this. The Schema Based XML representation contains references that can only be decoded by using the XML Schema that was used to encode the references. This appies to both Schema Based Binary XML and Object Relational Storage.