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
11
12
13 Example XML Schema
14
15
16
17
20
21
22
23
24
25
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
5 0
6
7 0
8 Text
9
10 '))
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
5 0
6
7 0
8 Text
9
10 '));
(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
5 0
6
7 0
8 Text
9
10 '));
1 row created.
SQL> insert into "VALIDATE_XML_SCHEMA"
2 values
3 (xmltype('< ?xml version="1.0" encoding="UTF-8"?>
4
5 0
6
7 0
8 Text
9
10 ').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
5 0
6
7 0
8 Text
9
10 ').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
0
0
Text
< ?xml version="1.0" encoding="UTF-8"?> 0
0
0
Text
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)
