One of the problems I saw lately was mentioning binary XML encoding, so I was able to help out more specific on these issues then normally when issues arise on the XMLDB Forum.
Two of them caught my attention:
- [11g] XML Schema full validation with binary XML?
- [11g] Adding a virtual column to a binary XML table?
Both are issues are related to the same person, Christian Soutou. I am not really sure what he wants to achieve, but the discussions brought me some new insights.
Adding a virtual column to a XMLType table
An simple example of the problem.
SQL> CREATE TABLE "VALIDATE_XML_SCHEMA" of XMLTYPE 2 XMLTYPE STORE AS BASICFILE BINARY XML 3 XMLSCHEMA "http://www.binary.com/root.xsd" 4 ELEMENT "ROOT" 5 DISALLOW NONSCHEMA 6 VIRTUAL COLUMNS (xmlrootid AS (extractvalue(object_value,'/ROOT/ID'))); TABLE created. SQL> ALTER TABLE "VALIDATE_XML_SCHEMA" 2 ADD 3 xmlinfoid 4 AS 5 (extractvalue(object_value,'/ROOT/INFO/INFO_ID')); xmlinfoid * ERROR at line 3: ORA-22856: cannot ADD COLUMNS TO object TABLES
The SQL Reference Guide – ALTER TABLE manual states:
virtual_column_definition
The virtual_column_definition has the same semantics when you add a column that it has when you create a column.
Restrictions on Adding Columns
The addition of columns is subject to the following restrictions:
- You cannot add a LOB column to a cluster table.
- If you add a LOB column to a hash-partitioned table, then the only attribute you can specify for the new partition is TABLESPACE.
- You cannot add a column with a NOT NULL constraint if table has any rows unless you also specify the DEFAULT clause.
- If you specify this clause for an index-organized table, then you cannot specify any other clauses in the same statement.
< /ul>
The problem, as Christian Antognini pointed out in the end, needs manual modification, because it will be supported in a future release (but currently isn’t). It is possible to use virtual columns with XMLType table constructs, but currently, you are not allowed to add new ones.
ORA-22856: cannot add columns to object tables
Cause: An attempt was made to add columns to an object table. Object tables cannot be altered to add columns since its definition is based on an object type.
Action: Create a new type with additional attributes, and use the new type to create an object table. The new object table will have the desired columns.
The only workaround is to create a new XMLType table with all virtual columns needed.
XML Schema validation on XMLType columns, tables
The second problem initially looked like if XML Schema validation wasn’t applicable when used with the option of virtual columns (at least that was what I thought at first glance). After an extensive search for answers and creating test cases, this proofed to be wrong.
The referenced XML Schema was not used because it will be only enforced via the DISALLOW NONSCHEMA option in combination with the XMLSCHEMA parameter.
A more detailed description and examples are demonstrated on the XMLDB Forum, an example is shown here regarding the outcome.
Be aware that my WordPress “code” plug-in still scrambles XML Schema notations
(so read the examples given by me on the forum: here)
/* ---------------------------------------------------- connect / as sysdba create user test identified by test account unlock; grant xdbadmin, dba to test; connect test/test ----------------------------------------------------- */ 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 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production 5 rows selected. SQL> SELECT schema_url, BINARY 2 FROM user_xml_schemas; no rows selected SQL> SELECT * FROM tab; no rows selected SQL> SELECT object_name, object_type FROM user_objects; no rows selected SQL> begin 2 :schemaURL := 'http://www.binary.com/root.xsd'; 3 :schemaPath := '/test/root_binary.xsd'; 4 end; 5 / PL/SQL procedure successfully completed. -- ================================================= -- Register Binary XML SChema -- ================================================= 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 xmlns="http://www.binary.com/root.xsd" targetNamespace="http://www.binary.com/root.xsd" 8 elementFormDefault="qualified" 9 attributeFormDefault="unqualified" 10 xdb:storeVarrayAsTable="true"> 11 </xs><xs :element name="ROOT" xdb:defaultTable="ROOT_TABLE" xdb:maintainDOM="false"> 12 </xs><xs :annotation> 13 </xs><xs :documentation>Example XML Schema</xs> 14 15 <xs :complexType xdb:maintainDOM="false"> 16 </xs><xs :sequence> 17 <xs :element name="ID" type="xs:integer" xdb:SQLName="ID"/> 18 <xs :element ref="INFO"/> 19 </xs> 20 21 22 <xs :element name="INFO" xdb:SQLName="INFO_TYPE"> 23 </xs><xs :complexType xdb:maintainDOM="false"> 24 </xs><xs :sequence> 25 <xs :element name="INFO_ID" type="xs:integer" xdb:SQLName="TYPE_INFO_ID"/> 26 <xs :element name="INFO_CONTENT" 27 xdb:SQLName="TYPE_INFO_CONTENT" type="xs:string"/> 28 </xs> 29 30 31 '); 32 begin 33 IF (dbms_xdb.existsResource(:schemaPath)) then 34 dbms_xdb.deleteResource(:schemaPath); 35 end IF; 36 res := dbms_xdb.createResource(:schemaPath,xmlSchema); 37 end; 38 / PL/SQL procedure successfully completed. SQL> SQL> ALTER session SET events='31098 trace name context forever'; Session altered. SQL> BEGIN 2 DBMS_XMLSCHEMA.registerSchema 3 (SCHEMAURL => :schemaURL, 4 SCHEMADOC => xdbURIType(:schemaPath).getClob(), 5 LOCAL => TRUE, -- local 6 GENTYPES => FALSE, -- generate object types 7 GENBEAN => FALSE, -- no java beans 8 GENTABLES => FALSE, -- generate object tables 9 OPTIONS => DBMS_XMLSCHEMA.REGISTER_BINARYXML, 10 OWNER => USER); 11 END; 12 / PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> SELECT schema_url, BINARY 2 FROM user_xml_schemas; SCHEMA_URL BIN -------------------------------- --- http://www.BINARY.com/root.xsd YES 1 row selected. SQL> purge recyclebin; Recyclebin purged. SQL> ALTER session SET recyclebin=OFF; Session altered. SQL> DROP TABLE VALIDATE_XML_SCHEMA; DROP TABLE VALIDATE_XML_SCHEMA * ERROR at line 1: ORA-00942: TABLE OR VIEW does NOT exist SQL> CREATE TABLE "VALIDATE_XML_SCHEMA" of XMLTYPE 2 XMLTYPE STORE AS BASICFILE BINARY XML 3 XMLSCHEMA "http://www.binary.com/root.xsd" 4 ELEMENT "ROOT" 5 DISALLOW NONSCHEMA; TABLE created. SQL> -- No schema defined SQL> INSERT INTO "VALIDATE_XML_SCHEMA" 2 VALUES 3 (xmltype('< ?xml version="1.0" encoding="UTF-8"?> 4 <root> 5 <id>0</id> 6 <info> 7 <info_id>0</info_id> 8 <info_content>Text</info_content> 9 </info> 10 </root>')) 11 ; (xmltype('< ?xml version="1.0" encoding="UTF-8"?> * ERROR at line 3: ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LSX-00021: undefined element "ROOT" SQL> drop table "VALIDATE_XML_SCHEMA" ; Table dropped. SQL> create table "VALIDATE_XML_SCHEMA" of XMLTYPE 2 XMLTYPE STORE AS BASICFILE BINARY XML 3 XMLSCHEMA "http://www.binary.com/root.xsd" 4 ELEMENT "ROOT" 5 DISALLOW NONSCHEMA 6 VIRTUAL COLUMNS (xmlrootid as (extractvalue(object_value,'/ROOT/ID'))); Table created. -- The following should fail -- No XSD reference given SQL> insert into "VALIDATE_XML_SCHEMA" 2 values 3 (xmltype('< ?xml version="1.0" encoding="UTF-8"?> 4 <root> 5 <id>0</id> 6 <info> 7 <info_id>0</info_id> 8 <info_content>Text</info_content> 9 </info> 10 </root>')); (xmltype('< ?xml version="1.0" encoding="UTF-8"?> * ERROR at line 3: ORA-31011: XML parsing failed ORA-19202: Error occurred IN XML processing LSX-00021: undefined element "ROOT" -- The following is correct -- Correct XSD namespace reference is given SQL> INSERT INTO "VALIDATE_XML_SCHEMA" 2 VALUES 3 (xmltype('< ?xml version="1.0" encoding="UTF-8"?> 4 <root xmlns="http://www.binary.com/root.xsd"> 5 <id>0</id> 6 <info> 7 <info_id>0</info_id> 8 <info_content>Text</info_content> 9 </info> 10 </root>')); 1 row created. SQL> INSERT INTO "VALIDATE_XML_SCHEMA" 2 VALUES 3 (xmltype('< ?xml version="1.0" encoding="UTF-8"?> 4 <root xmlns="http://www.binary.com/root.xsd"> 5 <id>0</id> 6 <info> 7 <info_id>0</info_id> 8 <info_content>Text</info_content> 9 </info> 10 </root>').CREATESCHEMABASEDXML('http://www.binary.com/root.xsd')); 1 row created. SQL> INSERT INTO "VALIDATE_XML_SCHEMA" 2 VALUES 3 (xmltype('< ?xml version="1.0" encoding="UTF-8"?> 4 <root> 5 <id>0</id> 6 <info> 7 <info_id>0</info_id> 8 <info_content>Text</info_content> 9 </info> 10 </root>').CREATESCHEMABASEDXML('http://www.binary.com/root.xsd')); (xmltype('< ?xml version="1.0" encoding="UTF-8"?> * ERROR at line 3: ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LSX-00021: undefined element "ROOT" SQL> commit; Commit complete. SQL> select xmlrootid from "VALIDATE_XML_SCHEMA"; XMLROOTID --------- 0 0 2 rows selected. SQL> select dbms_metadata.get_ddl('TABLE','VALIDATE_XML_SCHEMA') 2 from dual; DBMS_METADATA.GET_DDL('TABLE','VALIDATE_XML_SCHEMA') -------------------------------------------------------------------- CREATE TABLE "TEST"."VALIDATE_XML_SCHEMA" 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://www.binary.com/root.xsd" ELEMENT "ROOT" ID 14826 DISALLOW NONSCHEMA PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 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> desc user_tab_cols Name Null? Type ------------------------- -------- ----------------------- TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(106) DATA_TYPE_MOD VARCHAR2(3) DATA_TYPE_OWNER VARCHAR2(120) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR2(1) COLUMN_ID NUMBER DEFAULT_LENGTH NUMBER DATA_DEFAULT LONG NUM_DISTINCT NUMBER LOW_VALUE RAW(32) HIGH_VALUE RAW(32) DENSITY NUMBER NUM_NULLS NUMBER NUM_BUCKETS NUMBER LAST_ANALYZED DATE SAMPLE_SIZE NUMBER CHARACTER_SET_NAME VARCHAR2(44) CHAR_COL_DECL_LENGTH NUMBER GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) AVG_COL_LEN NUMBER CHAR_LENGTH NUMBER CHAR_USED VARCHAR2(1) V80_FMT_IMAGE VARCHAR2(3) DATA_UPGRADED VARCHAR2(3) HIDDEN_COLUMN VARCHAR2(3) VIRTUAL_COLUMN VARCHAR2(3) SEGMENT_COLUMN_ID NUMBER INTERNAL_COLUMN_ID NOT NULL NUMBER HISTOGRAM VARCHAR2(15) QUALIFIED_COL_NAME VARCHAR2(4000) SQL> select TABLE_NAME 2 , COLUMN_NAME 3 , DATA_TYPE 4 , HIDDEN_COLUMN 5 , VIRTUAL_COLUMN 6 from user_tab_cols 7 ; TABLE_NAME COLUMN_NAME DATA_TYPE HID VIR ------------------------------ ---------------- ----------- --- --- VALIDATE_XML_SCHEMA SYS_NC_ROWINFO$ XMLTYPE NO YES SYS_NC_OID$ RAW YES NO XMLDATA BLOB YES NO XMLROOTID NUMBER YES YES 4 rows selected. -- Regarding datatype "NUMBER" for XMLROOTID -- The element ID in XML Schema is defined as: -- [xs:element name="ID" type="xs:integer" xdb:SQLName="ID"/] SQL> col OBJECT_VALUE for a50 SQL> select object_value, XMLROOTID 2 from VALIDATE_XML_SCHEMA; OBJECT_VALUE XMLROOTID -------------------------------------------------- --------- < ?xml version="1.0" encoding="UTF-8"?> 0 <root xmlns="http://www.binary.com/root.xsd"> <id>0</id> <info> <info_id>0</info_id> <info_content>Text</info_content> </info> </root> < ?xml version="1.0" encoding="UTF-8"?> 0 <root xmlns="http://www.binary.com/root.xsd"> <id>0</id> <info> <info_id>0</info_id> <info_content>Text</info_content> </info> </root> 2 rows selected.
HTH
M. 
PS. Is there anywhere out there a WordPress plugin that not scrambles “XML Schemas” (my plug-in has no problem with ordinary XML)
