Oracle - validate date format (yyyy-mm-ddThh24:mi:ssZ) in XML against XSD

Question

Oracle version:

The result of this query select * from v$version; is:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE   11.2.0.4.0  Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Introduction to my situation:

I am creating a large xmltype with my procedure and I insert it in my table. Then I am trying to validate that created xmltype file against .xsd that I have registred in my database. I have succesfuly managed to shorten the xmltype data and the .xsd file so I can show you exactly on what line inside of the .xml file I am having problems.

Code that I have prepared you can copy and paste for testing:

This is my simple table:

create table XML_DATE_TEST(
    xml_file xmltype
);

And the procedure that is creating xmltype data and inserting it in this table is this:

CREATE OR REPLACE PROCEDURE P_XML_DATE_TEST (p_testvar in number) --
IS

    xml_help_variable xmltype;

BEGIN

    SELECT XMLELEMENT
           ("DocumentROOTTag", 
               XMLATTRIBUTES(
                   'http://www.w3.org/2001/XMLSchema-instance' "xmlns:xsi"
                   , 'XSD_TEST.xsd' "xsi:noNamespaceSchemaLocation"),
           XMLELEMENT
           ("SomeDateTag", 
           (to_char( sysdate,'yyyy-mm-dd')||'T'||to_char( sysdate,'hh24:mi:ss')||'Z'))
           )
    INTO xml_help_variable
    FROM dual
    WHERE p_testvar = 2;

INSERT INTO XML_DATE_TEST VALUES (xml_help_variable);

END P_XML_DATE_TEST;

Then I register my .xsd schema like this:

BEGIN
    DECLARE
        l_schema CLOB;
    BEGIN
        l_schema := '<?xml version="1.0" encoding="UTF-8"?>
                    <!--W3C Schema generated by XMLSpy v2009 sp1 (http://www.altova.com)-->
                    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
                        <xs:element name="DocumentROOTTag">
                            <xs:complexType>
                                <xs:sequence>
                                    <xs:element ref="SomeDateTag"/>
                                </xs:sequence>
                            </xs:complexType>
                        </xs:element>
                        <xs:element name="SomeDateTag">
                            <xs:simpleType>
                                <xs:restriction base="xs:dateTime"/>
                            </xs:simpleType>
                        </xs:element>
                    </xs:schema>';
        DBMS_XMLSCHEMA.registerSchema(schemaurl       => 'XSD_TEST.xsd', 
                                         schemadoc       => l_schema,
                                         local           => TRUE,
                                         gentypes        => FALSE,
                                         gentables       => FALSE,
                                         enablehierarchy => DBMS_XMLSCHEMA.enable_hierarchy_none); 
    END;
END;

And then I call my procedure:

BEGIN
    P_XML_DATE_TEST(2);
END;

After all of that I go and try to validate the created xmltype data from my table against .xsd file that I have registred. I try to do it in two ways:

1.By using isSchemaValid

SELECT x.xml_file.isSchemaValid('XSD_TEST.xsd')
FROM XML_DATE_TEST x;

2.By using schemaValidate

BEGIN
    DECLARE 
        XML XMLTYPE;
    BEGIN
        select x.xml_file.createSchemaBasedXML('XSD_TEST.xsd')
        INTO XML 
        from XML_DATE_TEST X;

        xmltype.schemaValidate(XML);

    END;
END;

Problem:

With the first methode that I use(isSchemaValid) the result that I get is 1. That means that my xmltype data is correct when I validate it against the provided .xsd schema. With the second methode that I use(schemaValidate) the result that I get is an error:

Error report -
ORA-30992: error occurred at Xpath /DocumentROOTTag/SomeDateTag
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at line 9
30992. 00000 -  "error occurred at Xpath %s"
*Cause:    
*Action:   See the following error and take appropriate action.

What I have tried:

When I remove the 'Z' part from the date format everything is ok but this is not a solution that is ok for me. The format of the date has to be like it is now.

Answer 1

According to the W3C XML Schema : Datatypes documentation time zones, and the zulu-time Z suffix, should be supported by the dateTime but in Oracle the xs:dateTime restriction appears to throw exceptions when the value contains a time zone [db<>fiddle]. It is not immediately obvious from the Oracle documentation that this is intended behaviour and the full dateTime restriction is not supported.

You could change the schema to use a regular expression rather than xs:dateTime. Its not an ideal solution as you either need a very complicated regular expression or accept that you may need to do some additional validation of dates to check no-one is entering an invalid date (i.e. 2019-02-29T...) that would otherwise pass the pattern matching.

DECLARE
  l_schema CLOB;
BEGIN
  l_schema := '<?xml version="1.0" encoding="UTF-8"?>
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="DocumentROOTTag">
        <xs:complexType>
          <xs:sequence>
            <xs:element ref="SomeDateTag"/>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
      <xs:element name="SomeDateTag">
        <xs:simpleType>
          <xs:restriction base="xs:string">
            <xs:pattern value="\d{4}-((0[1-9]|1[0-2])-(0[1-9]|[12]\d)|(0[469]|11)-30|(0[13578]|1[02])-3[01])T([0-1]\d|2[0-3]):[0-5]\d:[0-5]\d(\.\d+)?(Z|[+-]0\d:[0-5]\d|[+-]1[0-3]:[0-5]\d|[+-]14:00)?"/>
          </xs:restriction>
        </xs:simpleType>
      </xs:element>
    </xs:schema>';

  DBMS_XMLSCHEMA.registerSchema(
    schemaurl       => 'XSD_TEST.xsd', 
    schemadoc       => l_schema,
    local           => TRUE,
    gentypes        => FALSE,
    gentables       => FALSE,
    enablehierarchy => DBMS_XMLSCHEMA.enable_hierarchy_none
  ); 
END;
/

db<>fiddle

Answer 2

Something that is ok via isSchemaValid is not ok via schemaValidate

I think the problem here is that XML validation functions can be computationally very expensive so Oracle - like other implementations - looks to avoid work where it can.

The XMLDB documentation states that isSchemaValid() checks that ...

the input instance conforms to a specified schema. It does not change the validation status of the XML instance.

...but schemaValidate() ...

validates the XML instance against its schema ... [if successful] the document's status is changed to validated.

This is just a guess, but my interpretation is isSchemaValid() simply checks that the XML element is well-formed whereas schemaValidate() validates the well-formedness and the contents of the elements too. That is, I think isSchemaValid() is lighter than isSchemaValid().

That's why the best advice is to distrust XML until it's passed schemaValidate().

Of course, the more pressing problem is why Oracle's datetime doesn't allow for timezone notation when the XSD standards say it should. Another guess here, but I assume it's because Oracle maps xs:datetime to its DATE datatype, which doesn't support timezones.

Answer 3

You might slightly change your XSD by: 1) adding Oracle own namespace before registering a schema, 2) using 'SQLType' attribute to specify element's type for Oracle:

l_schema := '<?xml version="1.0" encoding="UTF-8"?>
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
     xmlns:xdb="http://xmlns.oracle.com/xdb">
      <xs:element name="DocumentROOTTag">
        <xs:complexType>
          <xs:sequence>
            <xs:element ref="SomeDateTag"/>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
      <xs:element name="SomeDateTag" xdb:SQLType="TIMESTAMP WITH TIME ZONE" >
        <xs:simpleType>
          <xs:restriction base="xs:dateTime"/>
        </xs:simpleType>
      </xs:element>
    </xs:schema>';

Any timezoned values will be validated as OK: db_fiddle

The same trick works for attributes. Instead of

    <xs:attribute name="SomeTimeStamp" type="xs:dateTime"/>

better use

    <xs:attribute name="SomeTimeStamp" xdb:SQLType="TIMESTAMP WITH TIME ZONE">
        <xs:simpleType>
          <xs:restriction base="xs:dateTime"/>
        </xs:simpleType>
    </xs:attribute>