Virtual Columns and Binary XML (Storage) Schema Validation

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:

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"></xs>
 18      <xs :element ref="INFO"></xs>
 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"></xs>
 26      <xs :element name="INFO_CONTENT" 
 27                  xdb:SQLName="TYPE_INFO_CONTENT" type="xs:string"></xs>
 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)

Marco Gralike Written by: