XMLDB and copyevolve - in at the deep end.....

XML in the database is one of those things that you know id there but you never really have cause to use it. When you do come to use it there are such a huge amount of features that it's difficult to even know where to begin. There are manuals with hundreds of pages about the myriad different ways to use the features.

In our case it's usage has been thrust upon us by out strategy team who have 'seen the light' and decided to go ahead a implement an xmldb solution. There are features you can use that are xml related without installing this component but we are going for the 'full' fat approach.

The first thing i got asked to look at was a problem with the copyevolve procedure which takes xml which complies with a schema (an xml one...) and evolves it to the next version of that xml schema (i.e. you might add a new mandatory element and you have to make all the old xml documents conform to that).

Talk about in at the deep end....

The issue was after the 'evolution' was complete they were tidying up the old schema which then seemed to be corrupting the database.

I set about creating a test case for oracle support - but first i had to figure out what the hell was actually happening in the first place.

The first thing i did was create a very basic xml schema document (xsd) - this just defines what xml has to look like.

In the case below i create a basic schema which has one main element - the xml must contain an attribute called attr1 which has to be of type string.

#cat /tmp/rich.xsd
<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
        <xs:element name="RICH" type="RICHTYPE" />
        <xs:complexType name="RICHTYPE" >
    <xs:sequence>
      <xs:element name="attr1" type="xs:string"/>
    </xs:sequence>
  </xs:complexType>
</xs:schema>

At the moment this is just a plain text file...

Oh and by the way this example assumes xmldb is already installed....

Now we create a user to do the test in (this can be any user name you like - i called it xdb_schem but that has nothing to do with the actual xdb install - it wasn't meant to be confusing - but reading it back it seems it is....)


SQL> create user xdb_schem identified by xdb_schem default tablespace sysaux;


User created.

We'll be lazy and give it DBA.

 SQL> grant dba to xdb_schem;


Grant succeeded.

Now we connect as the new user

 SQL> conn xdb_schem/xdb_schem

Now we read the file in from the filesystem (the example assumes you already have an oracle directory object called tmp pointing at /tmp). Once this is loaded into the l_xml variable we then call the registerschema procedure - this create a schema in the location 'http://test.com/rich.xsd' and create a table in the schema (an oracle one...) which will hold any xml we load into the database which complies with this schema.

declare
          l_xml xmltype;
          begin
            l_xml := xmltype(bfilename('TMP','rich.xsd'), nls_charset_id('UTF8'));
       dbms_xmlschema.registerschema(SCHEMAURL =>'http://test.com/rich.xsd',schemaDoc => l_xml,options => dbms_xmlschema.REGISTER_BINARYXML,genTypes=> false,GENTABLES=>true);
       end;
 /
PL/SQL procedure successfully completed.

The table that is created is system generated (though can be named by special syntax in the xsd) - the one this generated is shown below.

SQL> select tname from tab;

TNAME
--------------------------------------------------------------------------------
RICH659_TAB

Let's now load a basic xml document into this schema using the PLSQL below. The vitally important line is the part that says

    <RICH  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://test.com/rich.xsd">

This line must be exactly correct for the xml to be loaded and checked against the correct schema - if there is any typo you won't get an error and it just gets loaded into the default xmldb bucket with no xml/xsd checking - this is not obvious!


 DECLARE
          retb BOOLEAN;
 
    BEGIN
          retb := DBMS_XDB.createResource('/sys/schemas/XDB_SCHEM/test.com/test7.xml',
                                      '<?xml version="1.0"?>
    <RICH  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://test.com/rich.xsd">
      <attr1>test data</attr1></RICH>');
       END;
   /

PL/SQL procedure successfully completed.


To check it is loaded we do a quick count

SQL> select count(*) from RICH659_TAB;

  COUNT(*)
----------
         1

Now we load the same xsd (for the purposes of this test) but give it a different name in the xmldb repository

SQL> DECLARE
     res BOOLEAN;
   BEGIN
     res := DBMS_XDB_REPOS.createResource(
              '/sys/schemas/XDB_SCHEM/test.com/rich3.xsd',
              bfilename('TMP','rich.xsd'),
              nls_charset_id('AL32UTF8'));

   END;
  /

PL/SQL procedure successfully completed.

Now comes the evolution part - here we are saying we want to take all docs that are loaded against the original schema and convert them to the new schema. This is slightly artificial in this test case as they are the same - if they are different you define an xsl transformation whcih will convert from the old to the new format.

ANyway lets just convert.

SQL> BEGIN
         DBMS_XMLSCHEMA.copyEvolve(
           schemaURLs => xdb$string_list_t('http://test.com/rich.xsd'),
           newSchemas => XMLSequenceType(XDBURIType('/sys/schemas/XDB_SCHEM/test.com/rich3.xsd').getXML()));
       END;
   /

PL/SQL procedure successfully completed.

We can see it's done some funky stuff and created and dropped some table on the way - we've ended up with  a table of the same name with the data in it - but it's not the 'same' table.

SQL>  select tname from tab;

TNAME
--------------------------------------------------------------------------------
BIN$9azln1jLB87gQzop/QpH7Q==$0
RICH659_TAB
BIN$9azln1jKB87gQzop/QpH7Q==$0


It still contains our one doc though

SQL>  select count(*) from RICH659_TAB;

  COUNT(*)
----------
         1

Now we want to tidy up and remove the old schema (as it appears in sqldeveloper and is very confusing). The schemas get marked as hidden but sqldev still shows them. Lets remove the old one with some plsql.

SQL> declare
   schemaid raw(16);
   cursor c1 is select schema_id from user_xml_schemas where hidden='YES';
   begin
   open c1;
   loop
   fetch c1 into schemaid;
   exit when c1%notfound;

   DBMS_XMLSCHEMA.PURGESCHEMA(schemaid);
  end loop;
  end;
/
 
PL/SQL procedure successfully completed.

But.......

And this is the problem, purging the old schema also removed the storage table - which still wants to be used by the current schema. We can see the table has gone by running this select again

SQL> select count(*) from RICH659_TAB;
select count(*) from RICH659_TAB
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

And of course now trying to load some xml throws an error - which was the error the developers were seeing.

SQL> DECLARE
          retb BOOLEAN;
 
    BEGIN
          retb := DBMS_XDB.createResource('/sys/schemas/XDB_SCHEM/test.com/test7.xml',
                                      '<?xml version="1.0"?>
<RICH  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://test.com/rich.xsd">
  <attr1>test data</attr1></RICH>');
       END;
     /
DECLARE
*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "XDB.DBMS_XDB", line 174
ORA-06512: at line 5

Anyway we have a test case now for oracle - i don;t think the purge should drop the table - at least it should check if no other schemas are using it.....

We could prevent it by explicitly naming tables - but then we 'd have to explicitly give them different names. If we build functionality based on the table name then this will break....