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 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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.