DBMS_XMLSCHEMA_ANNOTATE.SETSQLTYPEMAPPING Doesn't Work For XSD ComplexType With DateTime (Doc ID 1610888.1)

Last updated on JULY 27, 2016

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


Following XML select query is showing output like:
SQL> select xmlserialize(content XML_FRAGMENT) from book_entity;
 <SecondVersionDate effectiveFrom="2012-04-01T02:13:00Z">2013-04-01T02:13:00.000000+00:00</SecondVersionDate>

This is expected behavior. xs:dateTime is stored in the database as timestamp with a default fractional
second precision of 6 and hence the result has 000000.
desc "DateTimeWithEffectiveDa632_T"
"DateTimeWithEffectiveDa632_T" is NOT FINAL
Name Null? Type
----------------------------------------- --------
effectiveFrom VARCHAR2(4000 CHAR)
To avoid this default timestamp behavior, in register Schema already using this sql line below to map datetime into varchar2 instead of timestamp.
That's why here 2011-04-01T02:13:00Z, Z is not expanded into six 0s.

But DBMS_XMLSCHEMA_ANNOTATE.SETSQLTYPEMAPPING (schema_inst,'xs:dateTime','VARCHAR2',TRUE); should work for all elements,

Why it just work for FirstVersionDate, and not work for SecondVersionDate.


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms