xmltype.schemaValidate changes timestamp values resulting in regex validation failure

Question

I'm having a problem validating a datetime value present a XML CLOB field in Oracle using a XSD that has a regular expression to validate the datetime accepted format.
Everything looks fine and the data is right but when applying the validation it fails because the validation seems to be changing the datetime value to a format with 6 trailing zeros, i.e., from "2024-03-08T11:56:23" to "2024-03-08T11:56:23.000000", for example

Here is some code to reproduce the behavior:

1. To register the XSD use the following code:

begin
    DBMS_XMLSCHEMA.registerSchema(schemaURL => 'testDateTimeWithZone.xsd',
        schemaDoc => '<?xml version="1.0" encoding="UTF-8"?>
        <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
        <xs:element name="EMPLOYEE" type="CHECK"/>
        <xs:complexType name="CHECK">
            <xs:sequence>
                <xs:element name="NAME" type="xs:string"/>
                <xs:element name="HIREDATE" type="dtTimeWithZone"/>
            </xs:sequence>
        </xs:complexType>
        <xs:simpleType name="dtTimeWithZone">
            <xs:restriction base="xs:dateTime">
                <xs:pattern value="\d*-\d{2}-\d{2}T(2[0-3]|[0-1][0-9]):[0-5][0-9]:[0-5][0-9](\.[0-9]{3})?(([\-+]((0[0-9])|(1[0-4])):[0-5][0-9])|Z)"/>
            </xs:restriction>
        </xs:simpleType>
        </xs:schema>');
end;
/

2. Run the following code to reproduce the failure

declare
    l_xmldoc xmltype;
begin
    dbms_output.put_line('Starting:');
    
    -- build some dummy VALID xml data
    select xmlelement("EMPLOYEE", 
        xmlelement("NAME", 'John'), 
        xmlelement("HIREDATE", '2024-03-08T11:56:23')    
    ) into l_xmldoc from dual;

    l_xmldoc := l_xmldoc.createSchemaBasedXML('testDateTimeWithZone.xsd');
    -- the validation fails!
    xmltype.schemaValidate(l_xmldoc);

    dbms_output.put_line('Finished!');
end;
/

3. Get rid of the XSD - or leave it if you whant :)

begin
    DBMS_XMLSCHEMA.deleteSchema('testDateTimeWithZone.xsd' ,DBMS_XMLSCHEMA.DELETE_CASCADE);
end;
/

I've also tried with XMLTYPE(l_xmldoc, 'testDateTimeWithZone.xsd'); but the problem is the same :(
Important note: I can't change XSD's regex
How can I manage to have a valid XSD validation?

Answer 1

Your code is ok except an incorrect type definition in your schema defining HIREDATE.

<xs:element name="HIREDATE" type="dtTimeWithZone"/>

If you change it to ....

<xs:element name="HIREDATE" type="xs:dateTime"/>

... it works ok. No need to change regex or anything else...
Resulting XML is...

<EMPLOYEE>
  <NAME>John</NAME>
  <HIREDATE>2024-03-08T11:56:23.000000</HIREDATE>
</EMPLOYEE>

... and if you pass the value with the zone - it will work fine too

...
        xmlelement("HIREDATE", '2024-03-08T11:56:23 +01:00')    
...

... it works fine with fractions (with or without zone) just the same

...
        xmlelement("HIREDATE", '2024-03-08T11:56:23.123 +01:00')    
...

If you try to pass invalid date (month = 13) ...

...
xmlelement("HIREDATE", '2024-13-08T11:56:23')
...
/*    R e s u l t :
ORA-30992: error occurred at Xpath /EMPLOYEE/HIREDATE
ORA-01843: not a valid month
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at line 14                                */