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
10
11
12 Example XML Schema
13
14
15
16
19
20
21
22
23
24
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
5
6 0
7
8 0
9 Text
10
11 ');
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$
--------------------------------------------------------------------------------
0
0
Text
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<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<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<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<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
