My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

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.


To view full details, 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 a vibrant support community of peers and Oracle experts.