How to be stupid – Learning from your Mistakes (aka Referencing Namespaces)

Sometimes I forget the stuff I have learned… And sometimes someone, I am lucky enough (in this case on the OTN XMLDB Forum), is so friendly to point out how silly I am ;-)

Last Friday I was testing Binary XMLType Tables in combination with use of an XMLIndex; Both using / being based on an XML Schema. The objects both used namespace references to the, for binary use, registered XML Schema. Whatever I did, it didn’t react as was described in the manuals.

It doesn’t help, that I know now that I should have been more observing to see what XMLSpy had generated as an example XML Instance document, nor the fact that I pinpointed to much on syntax and storage statements.

Luckily, in my mind, always conspicuous on stuff related to namespaces, I was so “smart” to not create a Oracle Support Request, but addressed it via the XMLDB OTN Forum… I was very embarrassed to read about (and afterwards had a good laugh about my mistake) as “Chandra” showed me my failure to see the simplest mistake…

The OTN post can be found here: XMLIndex – Namespace issue...

It at least points out my “rule of numb” that if a NAMESPACE is in place somehow AND you get BLANK DATA RETURNED, almost 99% of the cases (as far as I experienced it) are related to not using a namespace reference and/or using the wrong syntax / path to the namespace.

The OTN Forum does a better job in conserving my XML Schema syntax, so you probably should see it in full detail there.

Below the solution and the correct behavior demonstrated (mainly for reference).

Correct Demonstration

SQL> var schemaPath varchar2(256)
SQL> var schemaURL  varchar2(256)
 
SQL> BEGIN
  :schemaURL := 'http://localhost/public/xsd/binxsd.xsd';
  :schemaPath := '/public/myschema.xsd';
END;
/ 
 
PL/SQL PROCEDURE successfully completed.
 
SQL> CALL dbms_xmlSchema.deleteSchema(:schemaURL,4);
 
CALL completed.
 
SQL> DECLARE
  2    res BOOLEAN;
  3    xmlSchema xmlType := xmlType('< ?xml version="1.0" encoding="UTF-8" ?>
  4    5  <xs :schema xmlns="http://localhost/public/xsd/binxsd.xsd" targetNamespace="http://localhost/public/xsd/binxsd.xsd"
  6             xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"
  7             elementFormDefault="qualified"
  8             attributeFormDefault="unqualified"
  9             xdb:storeVarrayAsTable="true">
 10    </xs><xs :element name="ROOT" xdb:defaultTable="XMLBIN" xdb:maintainDOM="false">
 11       </xs><xs :annotation>
 12           </xs><xs :documentation>Example XML Schema</xs>
 13       
 14       <xs :complexType>
 15          </xs><xs :sequence>
 16              <xs :element name="ID" type="xs:integer" />
 17              <xs :element ref="INFO" />
 18          </xs>
 19       
 20    
 21    <xs :element name="INFO">
 22       </xs><xs :complexType>
 23           </xs><xs :sequence>
 24             <xs :element name="INFO_ID" type="xs:integer" />
 25             <xs :element name="INFO_CONTENT" />
 26           </xs>
 27       
 28    
 29  '
 30  );
 31  BEGIN
 32    IF (dbms_xdb.existsResource(:schemaPath)) THEN
 33        dbms_xdb.deleteResource(:schemaPath);
 34    END IF;
 35    res := dbms_xdb.createResource(:schemaPath,xmlSchema);
 36   END;
 37  / 
 
SQL> ALTER SESSION SET events='31098 trace name context forever';
 
SESSION altered.
 
SQL> DECLARE
  2    BINARY_XML BOOLEAN:=TRUE;
  3  BEGIN
  4     IF (BINARY_XML)
  5     THEN
  6        dbms_xmlschema.registerSchema(SCHEMAURL => :schemaURL,
  7                                      SCHEMADOC => xdbUriType(:schemaPath).getXML(),
  8                                      LOCAL     => TRUE,
  9                                      GENTYPES  => FALSE,
 10                                      GENBEAN   => FALSE,
 11                                      GENTABLES => FALSE,
 12                                      FORCE     => FALSE,
 13                                      OPTIONS   => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
 14                                      OWNER     => USER);
 15     ELSE
 16        dbms_xmlschema.registerSchema(SCHEMAURL => :schemaURL,
 17                                      SCHEMADOC => xdbUriType(:schemaPath).getXML(),
 18                                      LOCAL     => TRUE,
 19                                      GENTYPES  => TRUE,
 20                                      GENBEAN   => FALSE,
 21                                      GENTABLES => TRUE,
 22                                      FORCE     => FALSE,
 23                                      OWNER     => USER);
 24     END IF;
 25  END;
 26  / 
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col schema_url FOR a50
SQL> SELECT schema_url, BINARY FROM user_xml_schemas;
 
SCHEMA_URL                                         BIN
-------------------------------------------------- ---
http://localhost/public/xsd/binxsd.xsd             YES
 
SQL> DECLARE
  2     XMLData xmlType := xmlType(
  3     '< ?xml version="1.0" encoding="UTF-8"?>
  4     <!--Sample XML file generated by XMLSPY v2004 rel. 3 U (http://www.xmlspy.com)-->
  5     <root xmlns="http://localhost/public/xsd/binxsd.xsd">
  6        <id>0</id>
  7        <info>
  8           <info_id>0</info_id>
  9           <info_content>Text</info_content>
 10        </info>
 11     </root>');
 12  BEGIN
 13    FOR i IN 1..100
 14    loop
 15        INSERT INTO XMLBIN
 16        VALUES
 17        (XMLData);
 18    END loop;
 19  END;
 20  / 
 
PL/SQL PROCEDURE successfully completed.
 
SQL> commit;
 
SQL> SELECT COUNT(*) FROM XMLBIN;
 
  COUNT(*)
----------
       100
 
SQL> SET long 1000000
SQL> SET pages 50000
 
SQL> SELECT * FROM xmlbin WHERE rownum < 2
 
SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!--Sample XML file generated by XMLSPY v2004 rel. 3 U (http://www.xmlspy.com)-->
<root xmlns="http://localhost/public/xsd/binxsd.xsd">
  <id>0</id>
  <info>
    <info_id>0</info_id>
    <info_content>Text</info_content>
  </info>
</root>
 
SQL> UPDATE XMLBIN
  2  SET object_value = updateXML(object_value,
  3                              '/ROOT/INFO/INFO_ID/text()',
  4                              substr(round(dbms_random.VALUE*100),0,2)
  5                      );
 
100 ROWS updated.
 
SQL> 3
  3*                             '/ROOT/INFO/INFO_ID/text()',
SQL> c,/ROOT/INFO/INFO_ID,/ROOT/ID,
  3*                             '/ROOT/ID/text()',
SQL> r
  1  UPDATE XMLBIN
  2  SET object_value = updateXML(object_value,
  3                              '/ROOT/ID/text()',
  4                              substr(round(dbms_random.VALUE*100),0,2)
  5*                     )
 
100 ROWS updated.
 
SQL> CREATE INDEX XMLIX ON XMLBIN (object_value)
  2  INDEXTYPE IS XDB.XMLIndex
  3  PARAMETERS ('PATHS (INCLUDE (/ROOT/ID
  4                               /ROOT/INFO/INFO_ID
  5                              )
  6                      NAMESPACE MAPPING (xmlns="http://localhost/public/xsd/binxsd.xsd")
  7                     )
  8               PATH TABLE      XMLBIN_PATH_TABLE
  9               PATH ID INDEX   XMLBIN_PATHID_IX
 10               ORDER KEY INDEX XMLBIN_ORDERKEY_IX
 11             ');
 
 
INDEX created.
 
SQL> SELECT extractvalue(object_value,'/ROOT/ID') AS "ID"
  2  FROM   XMLBIN
  3  WHERE  rownum < 11
  4  ;
 
        ID
----------
        67
        45
        67
         2
         8
        25
        38
        36
        46
        16
 
10 ROWS selected.
 
SQL> SELECT extractvalue(object_value,'/ROOT/ID'
  2                                  , 'xmlns="http://localhost/public/xsd/binxsd.xsd"')
  3                     AS "ID"
  4  FROM   XMLBIN WHERE  rownum < 11
  5  ;
 
        ID
----------
        67
        45
        67
         2
         8
        25
        38
        36
        46
        16
 
10 ROWS selected.
 
SQL> SELECT extractvalue(object_value,'/ROOT/ID'
  2                                   , 'xmlns="http://localhost/binxsd.xsd"')
  3                     AS "ID"
  4  FROM   XMLBIN WHERE  rownum < 11
  5  ;
 
ID
--------------------------------------------------------------------------------
 
 
 
 
 
 
 
 
 
 
 
10 ROWS selected.
 
SQL> SELECT extractvalue(object_value,'/ROOT/INFO/INFO_ID') AS "INFO_ID"
  2  FROM   XMLBIN WHERE  rownum < 11;
 
   INFO_ID
----------
        63
        61
        75
         9
        32
        49
        30
        33
        45
        43
 
10 ROWS selected.
 
SQL> SELECT extractvalue(object_value,'/ROOT/INFO/INFO_ID'
  2                                   , 'xmlns="http://localhost/binxsd.xsd"')
  3                     AS "INFO_ID"
  4  FROM   XMLBIN WHERE  rownum < 11
  5  ;
 
INFO_ID
--------------------------------------------------------------------------------
 
 
 
 
 
 
 
 
 
 
 
10 ROWS selected.
 
SQL> SELECT extractvalue(object_value,'/ROOT/INFO/INFO_ID'
  2                                   ,'xmlns="http://localhost/public/xsd/binxsd.xsd"')
  3                    AS "INFO_ID"
  4  FROM   XMLBIN WHERE  rownum < 11
  5  ;
 
   INFO_ID
----------
        63
        61
        75
         9
        32
        49
        30
        33
        45
        43
 
10 ROWS selected.
SQL>  SELECT extractvalue(object_value,'/ROOT/INFO/INFO_CONTENT') AS "INFO_CONTENT"
  2  FROM   XMLBIN WHERE  rownum < 11;
 
INFO_CONTENT
--------------------------------------------------------------------------------
Text
Text
Text
Text
Text
Text
Text
Text
Text
Text
 
10 ROWS selected.
 
SQL> SELECT extractvalue(object_value,'/ROOT/INFO/INFO_CONTENT'
  2                                   ,'xmlns="http://localhost/public/xsd/binxsd.xsd"')
  3                      AS "INFO_CONTENT"
  4  FROM   XMLBIN WHERE  rownum < 11;
 
INFO_CONTENT
--------------------------------------------------------------------------------
Text
Text
Text
Text
Text
Text
Text
Text
Text
Text
 
10 ROWS selected.
 
SQL> SELECT extractvalue(object_value,'/ROOT/INFO/INFO_CONTENT'
  2                                   ,'xmlns="http://localhost//binxsd.xsd"')
  3                      AS "INFO_CONTENT"
  4  FROM   XMLBIN WHERE  rownum < 11;
 
INFO_CONTENT
--------------------------------------------------------------------------------
 
 
 
 
 
 
 
 
 
 
 
10 ROWS selected.

To demonstrate that it is using the XMLIndex…

SQL> SET LINES 200
SQL> r
  1  SELECT extractvalue(object_value,'/ROOT/INFO/INFO_ID'
  2                                   ,'xmlns="http://localhost/public/xsd/binxsd.xsd"')
  3                    AS "INFO_ID"
  4  FROM   XMLBIN WHERE  rownum < 11
  5*
 
   INFO_ID
----------
        63
        61
        75
         9
        32
        49
        30
        33
        45
        43
 
10 ROWS selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1876482572
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | ROWS  | Bytes | Cost (%CPU)| TIME     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    10 |   120 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| XMLBIN_PATH_TABLE |     1 |  3022 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | XMLBIN_PATHID_IX  |     1 |       |     1   (0)| 00:00:01 |
|*  3 |  COUNT STOPKEY              |                   |       |       |            |          |
|   4 |   TABLE ACCESS FULL         | XMLBIN            |   100 |  1200 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
   2 - access("SYS_P0"."PATHID"=HEXTORAW('51BB')  AND "SYS_P0"."RID"=:B1)
   3 - FILTER(ROWNUM&lt;11)
 
Note
-----
   - dynamic sampling used FOR this statement
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo SIZE
        520  bytes sent via SQL*Net TO client
        416  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
         10  ROWS processed
 
 
SQL>  SELECT extractvalue(object_value,'/ROOT/ID'
  2                                    ,'xmlns="http://localhost/public/xsd/binxsd.xsd"')
  3                     AS "ID"
  4   FROM   XMLBIN WHERE  rownum < 11
  5   ;
 
        ID
----------
        67
        45
        67
         2
         8
        25
        38
        36
        46
        16
 
10 ROWS selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1876482572
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | ROWS  | Bytes | Cost (%CPU)| TIME     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    10 |   120 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| XMLBIN_PATH_TABLE |     1 |  3022 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | XMLBIN_PATHID_IX  |     1 |       |     1   (0)| 00:00:01 |
|*  3 |  COUNT STOPKEY              |                   |       |       |            |          |
|   4 |   TABLE ACCESS FULL         | XMLBIN            |   100 |  1200 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
   2 - access("SYS_P0"."PATHID"=HEXTORAW('6223')  AND "SYS_P0"."RID"=:B1)
   3 - FILTER(ROWNUM&lt;11)
 
Note
-----
   - dynamic sampling used FOR this statement
 
 
Statistics
----------------------------------------------------------
         34  recursive calls
          0  db block gets
         86  consistent gets
          0  physical reads
          0  redo SIZE
        515  bytes sent via SQL*Net TO client
        416  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
         10  ROWS processed
 
 
SQL> SELECT extractvalue(object_value,'/ROOT/ID') AS "ID"
  2  FROM XMLBIN
  3  WHERE rownum < 11;
 
        ID
----------
        67
        45
        67
         2
         8
        25
        38
        36
        46
        16
 
10 ROWS selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1876482572
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | ROWS  | Bytes | Cost (%CPU)| TIME     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    10 |   120 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| XMLBIN_PATH_TABLE |     1 |  3022 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | XMLBIN_PATHID_IX  |     1 |       |     1   (0)| 00:00:01 |
|*  3 |  COUNT STOPKEY              |                   |       |       |            |          |
|   4 |   TABLE ACCESS FULL         | XMLBIN            |   100 |  1200 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
   2 - access("SYS_P0"."PATHID"=HEXTORAW('6223')  AND "SYS_P0"."RID"=:B1)
   3 - FILTER(ROWNUM&lt;11)
 
Note
-----
   - dynamic sampling used FOR this statement
 
 
Statistics
----------------------------------------------------------
         34  recursive calls
          0  db block gets
         86  consistent gets
          0  physical reads
          0  redo SIZE
        515  bytes sent via SQL*Net TO client
        416  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
         10  ROWS processed
 
SQL> SELECT extractvalue(object_value,'/ROOT/ID'
  2                                   ,'xmlns="http://localhost/ThisSchemaDoesNotExist.XSD"')
  3                     AS "ID"
  4  FROM XMLBIN
  5  WHERE rownum < 11
  6  ;
 
ID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 
 
 
 
 
 
 
 
 
 
10 ROWS selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1137229259
 
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |    10 | 20020 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |        |       |       |            |          |
|   2 |   TABLE ACCESS FULL| XMLBIN |   100 |   195K|     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER(ROWNUM&lt;11)
 
Note
-----
   - dynamic sampling used FOR this statement
 
 
Statistics
----------------------------------------------------------
         40  recursive calls
          0  db block gets
         84  consistent gets
          0  physical reads
          0  redo SIZE
        486  bytes sent via SQL*Net TO client
        416  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
         10  ROWS processed