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 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

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

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
----------------------------------------- --------
----------------------------
SYS_XDBPD$ XDB.XDB$RAW_LIST_T
SYS_XDBBODY$ TIMESTAMP(6)
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.
DBMS_XMLSCHEMA_ANNOTATE.SETSQLTYPEMAPPING (schema_inst,'xs:dateTime','VARCHAR2',TRUE);
 
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.

Cause

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