Schema validation not enforcing full datetime or timezone formats
335438Oct 5 2010 — edited Mar 21 2012We have a schema definition which requires that all date elements are in the format YYYY-MM-DDTHH:MM:SSZ (eg 2010-06-05T05:26:59Z),
so to register this in Oracle we have added xdb:SQLType="TIMESTAMP WITH TIME ZONE" against the date elements.
However if a date contains a value in YYYY-MM-DD format, then the schema passes validation in Oracle. This is incorrect as the same
xml fails validation in Altova XMLSpy. [http://www.w3schools.com/schema/schema_dtypes_date.asp] confirms that all components of the date
format are required.
To illustrate my point:
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 5 11:19:15 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> DECLARE
2 xmlschema CLOB := '<?xml version="1.0" encoding="UTF-8"?>
4 <xs:schema xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
5 <xs:complexType name="dateTypes" mixed="true" xdb:SQLType="DATES_T">
6 <xs:sequence>
7 <xs:element name="date" type="xs:date"/>
8 <xs:element name="dateTime" type="xs:dateTime"/>
9 <xs:element name="timestamp" type="xs:dateTime" xdb:SQLType="TIMESTAMP WITH TIME ZONE"/>
10 </xs:sequence>
11 </xs:complexType>
12 <xs:element name="Test" type="dateTypes" xdb:defaultTable="DATE_TABLE"/>
13 </xs:schema>' ;
14 BEGIN
15 dbms_xmlschema.registerSchema (schemaURL => 'Testdate.xsd',
16 schemaDoc => xmlschema,
17 local => TRUE,
18 genTypes => TRUE,
19 genBean => FALSE,
20 genTables => TRUE);
21 END;
22 /
PL/SQL procedure successfully completed.
SQL> DECLARE
2 xmlfile XMLTYPE := xmltype('<Test>
3 <date>2010-06-05</date>
4 <dateTime>2010-06-05T05:26:59</dateTime>
5 <timestamp>2010-06-05T05:26:59Z</timestamp>
6 </Test>') ;
7 BEGIN
8 xmlfile := xmlfile.createSchemaBasedXML ('Testdate.xsd');
9 xmlfile.schemaValidate ();
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SQL> DECLARE
2 xmlfile XMLTYPE := xmltype('<Test>
3 <date>2010-06-05</date>
4 <dateTime>2010-06-05</dateTime>
5 <timestamp>2010-06-05</timestamp>
6 </Test>') ;
7 BEGIN
8 xmlfile := xmlfile.createSchemaBasedXML ('Testdate.xsd');
9 xmlfile.schemaValidate ();
10 END;
11 /
PL/SQL procedure successfully completed.
The latter xml should fail. Validating this in XMLSpy it gives the error:
Value '2010-06-05' is not allowed for element <dateTime>.
Hint: A valid value would be '2001-12-17T09:30:47Z'.
Error location: Test / dateTime
Details
cvc-datatype-valid.1.2.1: For type definition 'xs:dateTime' the string '2010-06-05' does not match a literal in the lexical space of built-in type definition 'xs:dateTime'.
cvc-simple-type.1: For type definition 'xs:dateTime' the string '2010-06-05' is not valid.
cvc-type.3.1.3: The normalized value '2010-06-05' is not valid with respect to the type definition 'xs:dateTime'.
cvc-elt.5.2.1: The element <dateTime> is not valid with respect to the actual type definition 'xs:dateTime'.
Is there any way to enforce validation of the full format?
We are running Oracle 10.2.0.4 on Linux.
Jon