Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Validate XML: LSX-00333: literal "..." is not valid with respect to the pattern

MarwimFeb 4 2025 — edited Feb 5 2025

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;
/
Comments
Post Details
Added on Feb 4 2025
1 comment
194 views