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<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 |
Recent Comments