Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
→ code for the current problem and the working example at the end of the post
A long time ago I had a problem validating a XML containing a timestamp.
https://forums.oracle.com/ords/apexds/post/validate-xml-datetime-ora-01830-01858-7109
My current problems seems to be very similar but I just can't get it to work
The element to validate is a timestamp with three digits
<puegRequest:DatumAnfrage>2025-01-31T13:09:16.739</puegRequest:DatumAnfrage>
it is defined as
<xs:element name="DatumAnfrage" type="common:DatumZeitMilliSecType"/>
<xs:simpleType name="DatumZeitMilliSecType" xdb:SQLType="TIMESTAMP">
<xs:annotation>
<xs:documentation>Zeitstempel ohne Angabe der Zeitzone im XML Datumsformat</xs:documentation>
</xs:annotation>
<xs:restriction base="xs:dateTime">
<xs:pattern value="20[0-9]{2}-(12|11|10|0[1-9])-((3)(0|1)|(1|2)[0-9]|(0)[1-9])T([0-1][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9].[0-9]{3}"/>
</xs:restriction>
</xs:simpleType>
As you can see I annotated it as SQLType TIMESTAMP
I validate it with
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='yyyy-mm-dd"T"hh24:mi:ss.ff3';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
…
XMLTYPE.schemaValidate(xmldoc)
And get
ORA-31154: Ungültiges XML-Dokument → invalid XML-Document
ORA-19202: Fehler bei XML-Verarbeitung → Error occurred in XML processing
LSX-00333: Literal "2025-01-31T13:09:16.739000" ist bezogen auf Muster nicht gültig
→literal "…" is not valid with respect to the pattern
Any idea why it compares 6 digits. What is the difference to the working example in the other thread (see code below)?
The complete xsd can be found at Schemata PUEG für Arbeitgeber und Zahlstellen - Datenstelle der Rentenversicherung
My current example
BEGIN
dbms_xmlschema.registerSchema(
schemaURL => 'timestamp.xsd'
,schemaDoc => q'[<?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="DatumAnfrage" type="DatumZeitMilliSecType"/>
<xs:simpleType name="DatumZeitMilliSecType" xdb:SQLType="TIMESTAMP">
<xs:annotation>
<xs:documentation>Zeitstempel ohne Angabe der Zeitzone im XML Datumsformat</xs:documentation>
</xs:annotation>
<xs:restriction base="xs:dateTime">
<xs:pattern value="20[0-9]{2}-(12|11|10|0[1-9])-((3)(0|1)|(1|2)[0-9]|(0)[1-9])T([0-1][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9].[0-9]{3}"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>]'
,local => TRUE
,genTypes => FALSE
,genTables => FALSE
,enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
);
END;
/
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='yyyy-mm-dd"T"hh24:mi:ss.ff3';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
DECLARE
doc xmltype := xmltype('<DatumAnfrage>2012-06-02T09:30:47.123</DatumAnfrage>', 'timestamp.xsd');
BEGIN
doc.schemaValidate();
EXCEPTION
WHEN OTHERS THEN RAISE;
END;
/
The working example from the other thread
BEGIN
dbms_xmlschema.registerSchema(
schemaURL => 'timestamp.xsd'
,schemaDoc => q'[<?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="CreDtTm" type="ISODateTime" />
<xs:simpleType name="ISODateTime" xdb:SQLType="TIMESTAMP">
<xs:restriction base="xs:dateTime"/>
</xs:simpleType>
</xs:schema>]'
,local => TRUE
,genTypes => FALSE
,genTables => FALSE
,enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
);
END;
/
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='yyyy-mm-dd"T"hh24:mi:ss.ff3';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
DECLARE
doc xmltype := xmltype('<CreDtTm>2012-06-02T09:30:47.000</CreDtTm>', 'timestamp.xsd');
BEGIN
doc.schemaValidate();
EXCEPTION
WHEN OTHERS THEN RAISE;
END;
/