6 Replies Latest reply: Feb 27, 2013 5:04 PM by markjw RSS

    Can create view, but cannot create table / materialized view: Xpath is null

    markjw
      Hi all,

      We recently moved some XML documents into the database in an XMLType column and want to query the data. I've been writing some queries and turning them into materialized views. I got to one query, and something really strange is happening. My query returns the expected results, but I am getting an error when I try to create a materialized view out of it. Even stranger, I can create a view out of it, and I can manually insert its data into an existing table, but I cannot create a materialized view out of it and I cannot create a table out of it. Here is a brief summary, please let me know if anyone has suggestions.

      Issuing the following commands fails in SQLDeveloper

      -- Creating a materialized view out of the query fails:
      create materialized view element REFRESH COMPLETE ON DEMAND as [query];
      Error at Command Line:1 Column:1 SQL Error: ORA-31063: XPath compilation failed: Xpath is null.

      -- Creating a table out of the query with the following shortcut fails:
      create table element as [query];
      Error at Command Line:1 Column:1 SQL Error: ORA-31063: XPath compilation failed: Xpath is null.


      Issuing the following commands in SQLDeveloper works fine:

      -- Creating a view out of the query works:
      create or replace view element as [query];

      -- Creating a blank table from the query and then inserting data works:
      create table element as select * from [query] where 1 = 2;
      insert into element select * from [query];

      Here is a simplified version of the query...
      I have changed the names around, and cut the query down so maybe it will be a little easier to understand. I did confirm that this query is also having the same symptoms described above. Since I changed the names, executing the query returns no results. However creating a materialized view out of the query still fails with the 'Xpath is null' error.

      create materialized view element REFRESH COMPLETE ON DEMAND as
      select
      m.resource_id,
      xml.*
      from metadata_sources m,
      xmltable(
      'for $i in /metadata/app//*[(self::elem1 or self::elem2) and (parent::form or parent::subform)]
      let $formName := if($i/parent::subform) then $i/../../@name else $i/../@name
      let $subformName := if($i/parent::subform) then $i/../@name else ""
      return <data
      appId="{$i/ancestor::app[1]/idField}"
      formName="{$formName}"
      subformName="{$subformName}"
      elemName="{$i/@name}"></data>' passing m.xml_content
      columns
      app_id NUMBER path '@appId',
      form_name VARCHAR2(50 char) path '@formName',
      subform_name VARCHAR2(50 char) path '@subformName',
      elem_name VARCHAR2(50 char) path '@elemName'
      ) xml;

      Edited by: user11949534 on Feb 22, 2013 1:55 PM
        • 1. Re: Can create view, but cannot create table / materialized view: Xpath is null
          odie_63
          What's your database version please? (SELECT * FROM v$version)

          What's the storage type for the XMLType column? CLOB-based, Binary XML, OR ?
          • 2. Re: Can create view, but cannot create table / materialized view: Xpath is null
            markjw
            odie_63 wrote:
            What's your database version please? (SELECT * FROM v$version)
            SELECT * FROM v$version
            --------------------------------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
            PL/SQL Release 11.2.0.3.0 - Production
            CORE     11.2.0.3.0     Production
            TNS for Linux: Version 11.2.0.3.0 - Production
            NLSRTL Version 11.2.0.3.0 - Production

            odie_63 wrote:
            What's the storage type for the XMLType column? CLOB-based, Binary XML, OR ?
            I'm pretty sure the XMLType column is unstructured, CLOB-based. The column was just created as XMLTYPE, no registered XSD. I insert data through a java app with a JDBC call that uses the sql statement:

            insert into <table> values (XmlType(?)); (the parameter is a clob)
            • 3. Re: Can create view, but cannot create table / materialized view: Xpath is null
              odie_63
              I'm pretty sure the XMLType column is unstructured, CLOB-based. The column was just created as XMLTYPE, no registered XSD.
              If the table was created in 11.2.0.3 (or.2) and not migrated from a version lower than 11.2.0.2 then the XMLType storage you have is Binary XML.

              CLOB-based storage is deprecated, but if I force it with e.g.
              create table metadata_sources (resource_id number, xml_content xmltype)
              xmltype column xml_content store as basicfile clob ;
              then the MView is created correctly.
              So the problem is with Binary XML.

              As you may have noticed, if the MView is created over an empty master table then it works too, as well as the subsequent refreshes.

              The complexity of the XQuery also has a role, as it works with more simple expressions.

              Speaking of which, the XQuery can probably be rewritten using forward axes instead of parent and ancestor axes.
              If it doesn't solve the problem, it'll surely optimize the query.

              On a side note, what is your need for a materialized view?
              Have you thought about using a structured XMLIndex instead?
              • 4. Re: Can create view, but cannot create table / materialized view: Xpath is null
                markjw
                Thanks odie. I am new to XQuery and just went with the first thing I could get working (I was just googling XPath examples). I'll take a more comprehensive tutorial and try rewriting the queries as you suggested. I'll provide an update if that solves the issue.

                As far as creating a structured index, I was under the impression that I would need to then register an XSD. Unfortunately I don't have an XSD for the XML that I am storing. The amount of data being stored in this XML column is really not very much (the largest of the views that I am writing will have a few hundred rows). The XML data represents metadata from our application. Our requirement is to be able to reload the metadata on the fly. The path we are going is to load the XML into the database and pull the XML out and reprocess / reload it on request. The problem is that the current system replicates some of the metadata content into the database by having developers manually execute scripts that populate certain relational tables. The thought is that we would get rid of this step by writing the materialized views that mirror these old relational tables. The application would make a call to reload the materialized views whenever a request is made to 'reload' the application metadata.
                • 5. Re: Can create view, but cannot create table / materialized view: Xpath is null
                  odie_63
                  As far as creating a structured index, I was under the impression that I would need to then register an XSD.
                  No, you can use it without an XML schema.

                  How about a regular relational view, with an underlying xml index?
                  That way you also eliminate the need for an explicit refresh step as you would then be querying real-time data, as if it were relational data.

                  For example :
                  Connected to:
                  Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
                  
                  SQL> create table tmp_xml of xmltype ;
                  
                  Table created.
                  
                  SQL> insert into tmp_xml values (
                    2    xmlparse(document '<root><item id="1">ABC</item><item id="2">DEF</item></root>')
                    3  ) ;
                  
                  1 row created.
                  
                  SQL> insert into tmp_xml values (
                    2    xmlparse(document '<root><item id="3">GHI</item><item id="4">JKL</item></root>')
                    3  ) ;
                  
                  1 row created.
                  
                  SQL> create or replace view tmp_xml_v as
                    2  select x.item_id, x.item_val
                    3  from tmp_xml
                    4     , xmltable(
                    5         '/root/item' passing object_value
                    6         columns item_id  number      path '@id'
                    7               , item_val varchar2(3) path '.'
                    8       ) x
                    9  ;
                  
                  View created.
                  
                  SQL> create index tmp_xml_sxi on tmp_xml (object_value)
                    2  indextype is xdb.xmlindex
                    3  parameters (q'#
                    4  XMLTABLE tmp_xml_xtb '/root/item'
                    5  COLUMNS item_id  number      path '@id'
                    6        , item_val varchar2(3) path '.' #'
                    7  ) ;
                  
                  Index created.
                  
                  SQL> exec dbms_stats.gather_table_stats(user, 'TMP_XML');
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> set autotrace on explain
                  SQL> set lines 200
                  SQL> select * from tmp_xml_v ;
                  
                     ITEM_ID ITE
                  ---------- ---
                           3 GHI
                           4 JKL
                           1 ABC
                           2 DEF
                  
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 4168126828
                  
                  -------------------------------------------------------------------------------------------------------
                  | Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                  -------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT             |                        |     4 |   164 |     3   (0)| 00:00:01 |
                  |   1 |  NESTED LOOPS                |                        |       |       |            |          |
                  |   2 |   NESTED LOOPS               |                        |     4 |   164 |     3   (0)| 00:00:01 |
                  |   3 |    INDEX FULL SCAN           | SYS_C009273            |     2 |    34 |     1   (0)| 00:00:01 |
                  |*  4 |    INDEX RANGE SCAN          | SYS30366_30367_OID_IDX |     2 |       |     0   (0)| 00:00:01 |
                  |   5 |   TABLE ACCESS BY INDEX ROWID| TMP_XML_XTB            |     2 |    48 |     1   (0)| 00:00:01 |
                  -------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     4 - access("TMP_XML"."SYS_NC_OID$"="SYS_SXI_0"."OID")
                  • 6. Re: Can create view, but cannot create table / materialized view: Xpath is null
                    markjw
                    Thanks for posting that example, that is pretty neat. Looks like I still have a lot to learn regarding the XMLType abilities. I'm going to give some of your suggestions a shot and see if I can get it working.