This discussion is archived
5 Replies Latest reply: Jul 27, 2012 5:44 AM by Marwim RSS

Validate XML: DateTime -> ORA-01830 / 01858

Marwim Expert
Currently Being Moderated
Hello,

I try to parse an XML document that contains an element that is defined as DateTime
<CreDtTm>2012-06-02T09:30:47.000Z</CreDtTm>
The xsd is as follows
<xs:element name="CreDtTm" type="ISODateTime"/>
...
<xs:simpleType name="ISODateTime">
  <xs:restriction base="xs:dateTime"/>
</xs:simpleType>
To me the format looks correct, but I get
ORA-01830: date format picture ends before converting entire input string
I found http://stackoverflow.com/questions/6370035/why-dbms-xmlschema-fails-to-validate-a-valid-xsdatetime/6382096#6382096
and changed the schema:
<xs:simpleType name="ISODateTime" xdb:SQLType="TIMESTAMP WITH TIME ZONE">
Now I get
ORA-01858: a non-numeric character was found where a numeric was expected
So I tried
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"';
and tested
select systimestamp from dual;

SYSTIMESTAMP
----------------------------------------
2012-07-27T10:52:05.860Z
The exact format, but still ORA-01858.

The validation is done with
DECLARE
  xmldoc XMLTYPE;
DECLARE
  xmldoc XMLTYPE;
BEGIN
    xmldoc := XMLTYPE(q'[<?xml version="1.0" encoding="UTF-8"?>
      ...
      <CreDtTm>2012-06-02T09:30:47.000Z</CreDtTm>
      ...]');
    xmldoc := xmldoc.createSchemaBasedXML('http://...xsd');
    XMLTYPE.schemaValidate(xmldoc);
END;
Does anyone know where I'm wrong?
select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Regards
Marcus

Edited by: Marwim on 27.07.2012 11:45
Added db-version
  • 1. Validate XML:  DateTime -> ORA-01830 / 01858
    Marwim Expert
    Currently Being Moderated
    I created a little test case for everyone who wants to try it out
    BEGIN
        dbms_xmlschema.registerSchema(
             'http://test_datetime.xsd'
            ,q'[<?xml version="1.0" encoding="WE8ISO8859P1" standalone="no"?>
     <xs:schema xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.002.03" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" targetNamespace="urn:iso:std:iso:20022:tech:xsd:pain.001.002.03" elementFormDefault="qualified">
      <xs:element name="Document" type="Document"/>
        <xs:complexType name="Document">
            <xs:sequence>
                <xs:element name="CreDtTm" type="ISODateTime"/>
            </xs:sequence>
        </xs:complexType>
        <xs:simpleType name="ISODateTime" xdb:SQLType="TIMESTAMP WITH TIME ZONE">
            <xs:restriction base="xs:dateTime"/>
        </xs:simpleType>
    </xs:schema>]'
            );
    END;
    /
    
    DECLARE
      xmldoc XMLTYPE;
    
    BEGIN
      xmldoc := XMLTYPE(q'[<?xml version="1.0" encoding="UTF-8"?>
    <Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.002.03"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.001.002.03
    pain.001.002.03.xsd">
     <CreDtTm>2012-06-02T09:30:47.000Z</CreDtTm>
    </Document>]');
        xmldoc := xmldoc.createSchemaBasedXML('http://test_datetime.xsd');
        XMLTYPE.schemaValidate(xmldoc);
    END;
    /
    Regards
    Marcus
  • 2. Re: Validate XML:  DateTime -> ORA-01830 / 01858
    odie_63 Guru
    Currently Being Moderated
    Hi Marcus,

    This one's interesting.
    I had already set up my own little example before you post yours, so here's something that works for me :
    BEGIN
     
      dbms_xmlschema.registerSchema(
        schemaURL => 'test_ts.xsd'
      , schemaDoc => 
      
      '<?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 WITH TIME ZONE">
        <xs:restriction base="xs:dateTime"/>
      </xs:simpleType>
    </xs:schema>'
    
      , local => true
      , genTypes => false
      , genTables => false
      , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
      );
    
    END;
    /
    SQL> DECLARE
      2    doc xmltype := xmltype('<CreDtTm>2012-06-02T09:30:47.000Z</CreDtTm>', 'test_ts.xsd');
      3  BEGIN
      4    doc.schemaValidate();
      5  END;
      6  /
    DECLARE
    *
    ERROR at line 1:
    ORA-30992: error occurred at Xpath /CreDtTm[@SYS_XDBBODY$]
    ORA-01858: a non-numeric character was found where a numeric was expected
    ORA-06512: at "SYS.XMLTYPE", line 354
    ORA-06512: at line 4
    OK, same error so far.
    SQL> REM : change from ", " to ".,"
    SQL> ALTER SESSION SET nls_numeric_characters = '.,';
    
    Session altered.
    
    SQL> DECLARE
      2    doc xmltype := xmltype('<CreDtTm>2012-06-02T09:30:47.000Z</CreDtTm>', 'test_ts.xsd');
      3  BEGIN
      4    doc.schemaValidate();
      5  END;
      6  /
    
    PL/SQL procedure successfully completed.
    Now it works.

    My understanding is that there's an implicit conversion happening in addition to the automatic mapping "dateTime" --> "TIMESTAMP WITH TIME ZONE".
    The documentation, although not very clear, seems to hint in that direction :
    Using Trailing Z to Indicate UTC Time Zone

    XML Schema lets the time-zone component be specified as Z, to indicate UTC time zone. When a value with a trailing Z is stored in a SQL TIMESTAMP WITH TIME ZONE column, the time zone is actually stored as +00:00. Thus, the retrieved value contains the trailing +00:00, not the original Z. For example, if the value in the input XML document is 1973-02-12T13:44:32Z, the output is 1973-02-12T13:44:32.000000+00:00.
    So I think Oracle tries to expand "2012-06-02T09:30:47.000Z" to "2012-06-02T09:30:47.000000+00:00" but relying on the NLS session setting to determine the decimal separator used in the timestamp fractional part.
  • 3. Re: Validate XML:  DateTime -> ORA-01830 / 01858
    Marwim Expert
    Currently Being Moderated
    Thanks,

    it works, the validation does not stop because of a DateTime. Yet I get another error from my example, but this will be the subject of further investigation, possibly in another thread.

    Regards
    Marcus
  • 4. Re: Validate XML:  DateTime -> ORA-01830 / 01858
    odie_63 Guru
    Currently Being Moderated
    Yet I get another error from my example
    You mean this one ?
    ORA-31000: Resource 'pain.001.002.03.xsd' is not an XDB schema document
    That's because of the xsi:schemaLocation attribute, it takes precedence.
    You have to register the schema with this URL : "pain.001.002.03.xsd" and it'll work OK, and without having to build a schema-based instance beforehand :
    SQL> BEGIN
      2      dbms_xmlschema.registerSchema(
      3           'pain.001.002.03.xsd'
      4          ,q'[<?xml version="1.0" encoding="WE8ISO8859P1" standalone="no"?>
      5   <xs:schema xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.002.03" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" targetNamespace="urn:iso:std:iso:20022:tech:xsd:pain.001.002.03" elementFormDefault="qualified">
      6    <xs:element name="Document" type="Document"/>
      7      <xs:complexType name="Document">
      8          <xs:sequence>
      9              <xs:element name="CreDtTm" type="ISODateTime"/>
     10          </xs:sequence>
     11      </xs:complexType>
     12      <xs:simpleType name="ISODateTime" xdb:SQLType="TIMESTAMP WITH TIME ZONE">
     13          <xs:restriction base="xs:dateTime"/>
     14      </xs:simpleType>
     15  </xs:schema>]'
     16          , local => true
     17          , genTypes => false
     18          , genTables => false
     19      );
     20  END;
     21  /
     
    PL/SQL procedure successfully completed
     
    SQL> ALTER SESSION SET nls_numeric_characters = '.,';
     
    Session altered
     
    SQL> 
    SQL> DECLARE
      2    xmldoc XMLTYPE;
      3  
      4  BEGIN
      5    xmldoc := XMLTYPE(q'[<?xml version="1.0" encoding="UTF-8"?>
      6  <Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.002.03"
      7  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      8  xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.001.002.03 pain.001.002.03.xsd">
      9   <CreDtTm>2012-06-02T09:30:47.000Z</CreDtTm>
     10  </Document>]');
     11  
     12      XMLTYPE.schemaValidate(xmldoc);
     13  END;
     14  /
     
    PL/SQL procedure successfully completed
     
    BTW, if you're not planning to use Object-Relational storage but just the validation feature, set both genTypes and genTables to false.

    Edited by: odie_63 on 27 juil. 2012 14:00
  • 5. Re: Validate XML:  DateTime -> ORA-01830 / 01858
    Marwim Expert
    Currently Being Moderated
    That's because of the xsi:schemaLocation attribute, it takes precedence.
    You have to register the schema with this URL : "pain.001.002.03.xsd" and it'll work OK, and without having to build a schema-based instance beforehand :
    >

    Thanks, this saves me a lot of work. I got some training on XML but I'm still a beginner in using it effectively.
    BTW, if you're not planning to use Object-Relational storage but just the validation feature, set both genTypes and genTables to false.
    A useful tip. My DBA will be glad if I don't clutter up the schema with objects that will never be used.

    Regards
    Marcus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points