CODEWITHSUNDEEP
oracle-databasevalidationdatetimexsdoracle11g
user272735
user272735

Reputation: 10648

Why DBMS_XMLSCHEMA fails to validate a valid xs:dateTime?

I'm going to validate and process XML provided by a third party. The XML conforms a standardized XML schema also provided by a third party.

For validation I use DBMS_XMLSCHEMA essentially this way:

-- pseudocode follows
declare
  xmldoc xmltype;
begin
  dbms_xmlschema.registerschema(schemaurl => name,
                                schemadoc => xmltype(schema),
                                local => true,
                                gentypes => false,
                                gentables => false
                                );
  xmldoc := xmltype(xml).createSchemaBasedXML(schema_name);
  xmldoc.schemavalidate;
end;

Validation seems to work except that I've run an issue with XML schema type xs:dateTime that is demonstrated below.

With XML schema:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="datetime-issue">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="time" type="xs:dateTime" maxOccurs="unbounded"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

The validation of following XML:

<?xml version="1.0" encoding="UTF-8"?>
<datetime-issue>
  <time>2011-06-15T16:58:23</time>
  <!-- Oracle doesn't like timezone ? -->
  <time>2011-06-15T16:58:23+02:00</time>
  <time>2011-06-16T09:55:01</time>
</datetime-issue>

Fails unexpectedly on the second time-element:

ORA-30992: error occurred at Xpath /datetime-issue/time[2]
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "SYS.XMLTYPE", line 354

AFAICS 2011-06-15T16:58:23+02:00 should be a valid XML schema xs:dateTime value and DBMS_XMLSCHEMA should not complain about that at all. Validation should also be independent on any database date format settings, right ?

So is this an Oracle quirk and if yes what are the workarounds ? Or should I configure Oracle and/or DBMS_XMLSCHEMA differently ? Or have I misinterpreted something or ...

I'm running Oracle Database 11g Release 11.2.0.1.0.

If it's worth of anything the example XML schema and data above validates correctly with exchangerxml that uses Xerces.

Upvotes: 0

Views: 3561

Answers (2)

user272735
user272735

Reputation: 10648

This is just a collection of relevant Oracle documentation that answers to my question. I accepted Phil's answer as I found the right section of documentation based on that.

From chapter 7 XML Schema Storage and Query: Basic of Oracle XML DB Developer's Guide 11g Release 2 (11.2):

Working with Time Zones

The following XML Schema data types allow for an optional time-zone indicator as part of their literal values.

  • xsd:dateTime
  • xsd:time
  • xsd:date
  • xsd:gYear
  • xsd:gMonth
  • xsd:gDay
  • xsd:gYearMonth
  • xsd:gMonthDay

By default, XML schema registration maps xsd:dateTime and xsd:time to SQL data type TIMESTAMP and all the other data types to SQL data type DATE. SQL data types TIMESTAMP and DATE do not permit a time-zone indicator.

If your application needs to work with time-zone indicators, then use attribute SQLType to specify the SQL data type as TIMESTAMP WITH TIME ZONE. This ensures that values containing time-zone indicators can be stored and retrieved correctly. For example:

<element name="dob" type="xsd:dateTime"
      xdb:SQLType="TIMESTAMP WITH TIME ZONE"/>
<attribute name="endofquarter" type="xsd:gMonthDay"
       xdb:SQLType="TIMESTAMP WITH TIME ZONE"/>

Using Trailing Z to Indicate UTC Time Zone

XML Schema lets the time-zone component be specified as Z, to indicate UTC time zone. When a value with a trailing Z is stored in a SQL TIMESTAMP WITH TIME ZONE column, the time zone is actually stored as +00:00. Thus, the retrieved value contains the trailing +00:00, not the original Z. For example, if the value in the input XML document is 1973-02-12T13:44:32Z, the output is 1973-02-12T13:44:32.000000+00:00.

See also:

Upvotes: 0

Phil
Phil

Reputation: 2422

I think you can fix that by adding:

xdb:SQLType="TIMESTAMP WITH TIME ZONE"

to the element in the XML schema. Though that may error on the non-timezone records...

Upvotes: 1

Related Questions