Mark, the following irritates me to pieces (because I can't find the solution), but as you said once: "everything was easy until the introduced namespaces"...
The following is probably (AGAIN) a problem with addressing the wrong namespace (and maybe, just maybe, a bug)...
Is it a bug or were do I go wrong and/or when should one use "namespace mapping" comb. with an XMLIndex...
-- I already have a TEST and MARCO schema so now a different theme ;-)
SQL> create user mark identified by mark account unlock;
User created.
SQL> grant dba, xdbadmin to mark;
Grant succeeded.
SQL> conn mark/mark
Connected.
SQL> col schema_url for a50
SQL> set long 10000000
SQL> set pages 5000
SQL> select * from user_xml_schemas;
no rows selected
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);
select * from tab;call dbms_xmlSchema.deleteSchema(:schemaURL,4)
*
ERROR at line 1:
ORA-31000: Resource 'http://localhost/public/xsd/binxsd.xsd' is not an XDB
schema document
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 106
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 102
ORA-06512: at line 1
SQL> declare
2 res boolean;
3 xmlSchema xmlType := xmlType(
4 '<?xml version="1.0" encoding="UTF-8" ?>
5 <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
6 xmlns:xdb="http://xmlns.oracle.com/xdb"
7 elementFormDefault="qualified"
8 attributeFormDefault="unqualified"
9 xdb:storeVarrayAsTable="true">
10 <xs:element name="ROOT" xdb:defaultTable="ROOT_TBL" xdb:maintainDOM="false">
11 <xs:annotation>
12 <xs:documentation>Example XML Schema</xs:documentation>
13 </xs:annotation>
14 <xs:complexType>
15 <xs:sequence>
16 <xs:element name="ID" type="xs:integer" />
17 <xs:element ref="INFO" />
18 </xs:sequence>
19 </xs:complexType>
20 </xs:element>
21 <xs:element name="INFO">
22 <xs:complexType>
23 <xs:sequence>
24 <xs:element name="INFO_ID" type="xs:integer" />
25 <xs:element name="INFO_CONTENT" />
26 </xs:sequence>
27 </xs:complexType>
28 </xs:element>
29 </xs:schema>'
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);
end;
36 37 /
PL/SQL procedure successfully completed.
SQL> alter session set events='31098 trace name context forever';
Session altered.
SQL> 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> select schema_url, binary from user_xml_schemas;
SCHEMA_URL BIN
-------------------------------------------------- ---
http://localhost/public/xsd/binxsd.xsd YES
SQL> CREATE TABLE XMLBIN OF XMLTYPE
2 XMLTYPE STORE AS BASICFILE BINARY XML
3 XMLSCHEMA "http://localhost/public/xsd/binxsd.xsd" ELEMENT "ROOT"
4 ;
Table created.
SQL> select dbms_metadata.get_ddl('TABLE','XMLBIN') from dual;
DBMS_METADATA.GET_DDL('TABLE','XMLBIN')
--------------------------------------------------------------------------------
CREATE TABLE "MARK"."XMLBIN" OF "SYS"."XMLTYPE"
XMLTYPE STORE AS BASICFILE BINARY XML (
TABLESPACE "USERS" ENABLE STORAGE IN ROW
CHUNK 8192
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT))
XMLSCHEMA "http://localhost/public/xsd/binxsd.xs
d" ELEMENT "ROOT" ID 4460 DISALLOW NONSC
HEMA PCTFREE 10 PCTUSED 40 INITRANS 1 MA
XTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT)
TABLESPACE "USERS"
SQL> col segment_name for a40
SQL> comp sum of "BYTES(MB)" on report
SQL> break on report
SQL> select segment_name
2 , segment_type
3 , (bytes/(1024*1024)) "BYTES(MB)"
4 from user_segments
5 order by segment_type;
SEGMENT_NAME SEGMENT_TYPE BYTES(MB)
---------------------------------------- ------------------ ----------
SYS_C008595 INDEX .0625
SYS_IL0000067630C00003$$ LOBINDEX .0625
SYS_LOB0000067630C00003$$ LOBSEGMENT .0625
XMLBIN TABLE .0625
----------
sum .25
-- Inserting data
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:xdb="http://xmlns.oracle.com/xdb"
6 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
7 xsi:noNamespaceSchemaLocation="http://localhost/public/xsd/binxsd.xsd">
8 <ID>0</ID>
9 <INFO>
10 <INFO_ID>0</INFO_ID>
11 <INFO_CONTENT>Text</INFO_CONTENT>
12 </INFO>
13 </ROOT>');
14 BEGIN
15 for i in 1..100
16 loop
17 insert into XMLBIN
18 VALUES
19 (XMLData);
20 end loop;
21 END;
22 /
PL/SQL procedure successfully completed.
-- Making it a little bit more random
SQL> 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> 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> commit;
Commit complete.
SQL> select segment_name
2 , segment_type
3 , (bytes/(1024*1024)) "BYTES(MB)"
4 from user_segments
5 order by segment_type;
SEGMENT_NAME SEGMENT_TYPE BYTES(MB)
---------------------------------------- ------------------ ----------
SYS_C008595 INDEX .0625
SYS_IL0000067630C00003$$ LOBINDEX .0625
SYS_LOB0000067630C00003$$ LOBSEGMENT .0625
XMLBIN TABLE .0625
----------
sum .25
SQL> select count(*) from XMLBIN;
COUNT(*)
----------
100
SQL> select extractvalue(object_value,'/ROOT/ID') as "ID"
2 from XMLBIN
3 where rownum < 11
4 ;
ID
----------
93
64
21
68
72
33
81
76
83
68
10 rows selected.
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:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/
XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost/public/xsd/b
inxsd.xsd">
<ID>93</ID>
<INFO>
<INFO_ID>59</INFO_ID>
<INFO_CONTENT>Text</INFO_CONTENT>
</INFO>
</ROOT>
-- Create XMLIndex with namespace mapping
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 segment_name
2 , segment_type
3 , (bytes/(1024*1024)) "BYTES(MB)"
4 from user_segments
5 order by segment_type;
SEGMENT_NAME SEGMENT_TYPE BYTES(MB)
---------------------------------------- ------------------ ----------
XMLBIN_ORDERKEY_IX INDEX .0625
SYS_C008595 INDEX .0625
SYS67634_XMLIX_VALUE_IX INDEX .0625
XMLBIN_PATHID_IX INDEX .0625
SYS_IL0000067630C00003$$ LOBINDEX .0625
SYS_LOB0000067630C00003$$ LOBSEGMENT .0625
XMLBIN TABLE .0625
XMLBIN_PATH_TABLE TABLE .0625
----------
sum .5
8 rows selected.
SQL> select extractvalue(object_value,'/ROOT/ID') as "ID"
2 from XMLBIN
3 where rownum < 11
4 ;
ID
----------
10 rows selected.
SQL> select extractvalue(object_value,'/ROOT/INFO/INFO_CONTENT') as "CONTENT"
2 from XMLBIN where rownum < 11
3 ;
CONTENT
--------------------------------------------------------------------------------
Text
Text
Text
Text
Text
Text
Text
Text
Text
Text
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
--------------------------------------------------------------------------------
10 rows selected.
-- I tried a lot of different scenarios but haven't found the solution yet...
-- Is my namespace wrong...???
SQL> select schema_url, binary from user_xml_schemas;
SCHEMA_URL BIN
-------------------------------------------------- ---
http://localhost/public/xsd/binxsd.xsd YES
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production