XML validation with xsd doesn't work with dates

Ants Hindpere

Oracle version 19.11

Simple xsd

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>                       
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
  <xs:element name="test" type="ISODate"/>
    <xs:simpleType name="ISODate">
    <xs:restriction base="xs:date">
      <xs:pattern value="[0-9]{4,4}\-[0-9]{2,2}\-[0-9]{2,2}"/>
    </xs:restriction>
    </xs:simpleType>
    </xs:schema>

When xml is

<test>10-JAN-10</test>

schemavalidate doesn't raise an error that it's invalid value.

Full example:

--exec dbms_xmlschema.deleteschema('test.xsd', 3);


declare
    l_content clob:= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>                                             
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
    <xs:element name="test" type="ISODate"/>
        <xs:simpleType name="ISODate">
        <xs:restriction base="xs:date">
            <xs:pattern value="[0-9]{4,4}\-[0-9]{2,2}\-[0-9]{2,2}"/>
        </xs:restriction>
        </xs:simpleType>
        </xs:schema>
';
begin
    dbms_xmlschema.registerschema(
        schemaurl => 'test.xsd'
      , schemadoc => l_content
      , local => true
      , gentypes => false
      , gentables => false
      , genbean => false
      , force => false
    );
end;
/


declare
    l_test xmltype :=xmltype('<test>10-JAN-10</test>');
    l_xml xmltype;
begin
    l_xml:=l_test.createSchemaBasedXML('test.xsd');
    l_xml.schemaValidate();
end;
/

However using xmlint

<test>10-JAN-10</test>
test.xml:1: element test: Schemas validity error : Element 'test': '10-JAN-10' is not a valid value of the atomic type 'ISODate'.
test.xml fails to validate

What is missing to validate the xml correctly?

odie_63

You have to register for Binary XML for the pattern facet validation to work.

Don't ask me why. The regular registration seems to ignore those additional restrictions and use some SQL date validation internally, which is also dependent on NLS settings it would appear.

  dbms_xmlschema.registerschema(
     ...
   , options => dbms_xmlschema.REGISTER_BINARYXML
  );

then:

declare
  l_test xmltype :=xmltype('<test>10-JAN-10</test>');
  l_xml xmltype;
begin
  l_xml:=l_test.createSchemaBasedXML('test.xsd');
  l_xml.schemaValidate();
end;
/
 
ORA-31154: invalid XML document
ORA-19202: Error occurred in XML processing
LSX-00249: invalid date or time "10-JAN-10"
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at line 7
 

Jason_(A_Non)

From what I recall and could verify via a very old post

https://community.oracle.com/tech/apps-infra/discussion/418458/how-does-xml-db-handle-xml-schema-validation

you need to add xsi:noNamespaceSchemaLocation="test.xsd" to your root node.

Ants Hindpere

Hi,

also doesn't work. Still doesn't raise an error that date is invalid.

declare
    l_test xmltype :=xmltype('<test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="test.xsd">10-JAN-10</test>');
    l_xml xmltype;
begin
    l_xml:=l_test.createSchemaBasedXML('test.xsd');
    l_xml.schemaValidate();
end;
/

xsi is probably also not needed when specifying it already createSchemaBasedXML('test.xsd')

odie_63

You have to register for Binary XML for the pattern facet validation to work.

Don't ask me why. The regular registration seems to ignore those additional restrictions and use some SQL date validation internally, which is also dependent on NLS settings it would appear.

  dbms_xmlschema.registerschema(
     ...
   , options => dbms_xmlschema.REGISTER_BINARYXML
  );

then:

declare
  l_test xmltype :=xmltype('<test>10-JAN-10</test>');
  l_xml xmltype;
begin
  l_xml:=l_test.createSchemaBasedXML('test.xsd');
  l_xml.schemaValidate();
end;
/
 
ORA-31154: invalid XML document
ORA-19202: Error occurred in XML processing
LSX-00249: invalid date or time "10-JAN-10"
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at line 7
 

odie_63

you need to add xsi:noNamespaceSchemaLocation="test.xsd" to your root node.

You would need that in order to call createSchemaBasedXML() without any argument.

Ants Hindpere

options => dbms_xmlschema.REGISTER_BINARYXML

That solved by problem. Thanks!