5 Replies Latest reply: Jul 27, 2012 7:44 AM by Marwim RSS

    Validate XML: DateTime -> ORA-01830 / 01858

    Marwim
      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
          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
            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
              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
                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
                  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