Oracle: how to create a fast refresh materialized view that extracts data from XMLType?

Question

I have a table xml_documents with two columns: a document_id column (primary key) and an xml column with some XML data, which is a schema-less XMLType. I can create a materialized view with just the document_id with:

create materialized view mv refresh fast on commit as 
select document_id
from xml_documents

This works fine, but isn't very useful. As you might expect, I'd like the materialized view to extract data from the XML, and for this I use extractValue(). I am trying the following:

create materialized view mv refresh fast on commit as 
select document_id, extractValue(xml, '/my/gaga') gaga
from xml_documents

This fails with:

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

How should I go about to create a fast refresh on commit materialized view that extract values from XML?

Answer 1

Your XMLType is (probably) stored as a CLOB. Find the hidden column with a query like this:

select * from user_tab_cols where table_name = 'XML_DOCUMENTS';

Then create a function to convert a CLOB into an XMLType, and extract the value. Note that the "deterministic" keyword is necessary, although I'm not sure why. Passing data back and forth between SQL and PL/SQL will be slow, but if you're using a materialized view things are probably already slow.

create or replace function extract_from_clob(p_xml in clob) return varchar2 deterministic
  is
begin
    return XMLType(p_xml).extract('/my/gaga/text()').getStringVal();
end;
/

Then drop and create your materialized view with the system column passed into the function:

create materialized view mv refresh fast on commit as 
select document_id, extract_from_clob(SYS_NC00003$) gaga
from xml_documents;

I'm unsure about using a system-generated hidden column. It works, but doesn't seem like a really good idea. At the very least it will make it difficult to create the object on different systems - you'll need to find the new column name each time.

It seems weird that XMLTypes don't work when LOBs work fine. I can't find any documentation about this; I'm not sure if it's a bug, an unimplemented feature, or if there's some magic setting that will make it work. If no one else can provide a better answer, it might be worth checking with Oracle support before you use the above approach.