Tom,
I am having real problems validating a date with a timezone against a schema, when the schema is loaded into Oracle. My schema uses the xdb:SQLType="TIMESTAMP WITH TIME ZONE" , and creates a field of type TIMESTAMP WITH TZ . This is what I would expect.
However when I validate xml against that schema I can only see to get one format that is an xs:date standard to work and that is 2006-04-18T13:45:45.000000 I was hoping to able to use 2006-04-18T13:45:45+09:00 which is xs:date standard. I am sure that all the dates I am using below are valis xs:date formats.
your thoughts would be much appreciated.
Please find all examples and schemas below.
Oracle rel 9.2.0.6
Regards
Phil
----------------
var schemaURL varchar2(256);
var schemaPath varchar2(256);
begin
:schemaURL := 'dateTimeTest.xsd';
:schemaPath := '/public/dateTimeTest.xsd';
end;
/
PL/SQL procedure successfully completed
call dbms_xmlSchema.deleteSchema(:schemaURL,4);
Call completed.
declare
res boolean;
xmlSchema xmlType := xmlType('<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="</code>
http://www.w3.org/2001/XMLSchema" xmlns:xdb="
http://xmlns.oracle.com/xdb" > <code>
<xs:element name="root" xdb:defaultTable="DATE_TEST_TABLE">
<xs:annotation>
<xs:documentation>Comment describing your root element</xs:documentation>
</xs:annotation>
<xs:complexType xdb:SQLType="DATE_TEST_T">
<xs:sequence>
<xs:element minOccurs="0" name="date" type="xs:date" xdb:SQLType="DATE"/>
<xs:element minOccurs="0" name="dateTime" type="xs:dateTime" xdb:SQLType="TIMESTAMP"/>
<xs:element minOccurs="0" name="dateTimeTZ" type="xs:dateTime" xdb:SQLType="TIMESTAMP WITH TIME ZONE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
');
begin
begin
dbms_xdb.deleteResource(:schemaPath);
end;
res := dbms_xdb.createResource(:schemaPath,xmlSchema);
end;
/
PL/SQL procedure successfully completed
begin
dbms_xmlschema.registerSchema
(
:schemaURL,
xdbURIType(:schemaPath).getClob(),
true,true,false,true
);
end;
/
PL/SQL procedure successfully completed
desc DATE_TEST_T;
Element Type
---------- ------------------
SYS_XDBPD$ XDB.XDB$RAW_LIST_T
date DATE
dateTime TIMESTAMP
dateTimeTZ TIMESTAMP WITH TZ
declare
xmldoc xmltype :=xmltype('
<root><dateTimeTZ>2006-04-18T13:45:45.000000</dateTimeTZ></root>','dateTimeTest.xsd'
);
begin
xmldoc.schemavalidate();
if xmldoc.isschemavalidated() = 1 then
dbms_output.put_line('Data is valid');
else
dbms_output.put_line('Data is invalid');
end if;
end;
/
Data is valid
PL/SQL procedure successfully completed
declare
xmldoc xmltype :=xmltype('
<root><dateTimeTZ>2006-04-18T13:45:45.0Z</dateTimeTZ></root>','dateTimeTest.xsd'
);
begin
xmldoc.schemavalidate();
if xmldoc.isschemavalidated() = 1 then
dbms_output.put_line('Data is valid');
else
dbms_output.put_line('Data is invalid');
end if;
end;
/
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 6
declare
xmldoc xmltype :=xmltype('
<root><dateTimeTZ>2006-04-18T13:45:45Z</dateTimeTZ></root>','dateTimeTest.xsd'
);
begin
xmldoc.schemavalidate();
if xmldoc.isschemavalidated() = 1 then
dbms_output.put_line('Data is valid');
else
dbms_output.put_line('Data is invalid');
end if;
end;
/
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 6
declare
xmldoc xmltype :=xmltype('
<root><dateTimeTZ>2006-04-18T13:45:45.0-08:00</dateTimeTZ></root>','dateTimeTest.xsd'
);
begin
xmldoc.schemavalidate();
if xmldoc.isschemavalidated() = 1 then
dbms_output.put_line('Data is valid');
else
dbms_output.put_line('Data is invalid');
end if;
end;
/
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 6
declare
xmldoc xmltype :=xmltype('
<root><dateTimeTZ>2006-04-18T13:45:45-08:00</dateTimeTZ></root>','dateTimeTest.xsd'
);
begin
xmldoc.schemavalidate();
if xmldoc.isschemavalidated() = 1 then
dbms_output.put_line('Data is valid');
else
dbms_output.put_line('Data is invalid');
end if;
end;
/
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 6