Skip to Main Content
  • Questions
  • Oracle - validate date format (yyyy-mm-ddThh24:mi:ssZ) in XML against XSD

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Vladimir.

Asked: October 23, 2019 - 3:45 pm UTC

Answered by: Connor McDonald - Last updated: October 25, 2019 - 5:45 am UTC

Category: PL/SQL - Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production" TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Partitioning an existing index

You Asked

Oracle version:

The result of this query select * from v$version; is:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE   11.2.0.4.0  Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


Introduction to my situation:

I am creating a large xmltype with my procedure and I insert it in my table. The I am trying to validate that xmltype file against .xsd that I have registred in my database. I have succesfuly managed to shorten the xmltype data and the .xsd file so I can show you exactly on what line inside of the .xml file I am having problems.

Code that I have prepared you can copy and paste for testing(but you can not use it like this on LiveSQL):

This is my simple table:

create table XML_DATE_TEST(
    xml_file xmltype
);


And the procedure that is creating xmltype data and inserting it in this table is this:

CREATE OR REPLACE PROCEDURE P_XML_DATE_TEST (p_testvar in number) --
IS

    xml_help_variable xmltype;

BEGIN

    SELECT XMLELEMENT
           ("DocumentROOTTag", 
               XMLATTRIBUTES(
                   'http://www.w3.org/2001/XMLSchema-instance' "xmlns:xsi"
                   , 'XSD_TEST.xsd' "xsi:noNamespaceSchemaLocation"),
           XMLELEMENT
           ("SomeDateTag", 
           (to_char( sysdate,'yyyy-mm-dd')||'T'||to_char( sysdate,'hh24:mi:ss')||'Z'))
           )
    INTO xml_help_variable
    FROM dual
    WHERE p_testvar = 2;

INSERT INTO XML_DATE_TEST VALUES (xml_help_variable);

END P_XML_DATE_TEST;


Then I register my .xsd schema like this:

BEGIN
    DECLARE
        l_schema CLOB;
    BEGIN
        l_schema := '<?xml version="1.0" encoding="UTF-8"?>
                    <!--W3C Schema generated by XMLSpy v2009 sp1 (http://www.altova.com)-->
                    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
                        <xs:element name="DocumentROOTTag">
                            <xs:complexType>
                                <xs:sequence>
                                    <xs:element ref="SomeDateTag"/>
                                </xs:sequence>
                            </xs:complexType>
                        </xs:element>
                        <xs:element name="SomeDateTag">
                            <xs:simpleType>
                                <xs:restriction base="xs:dateTime"/>
                            </xs:simpleType>
                        </xs:element>
                    </xs:schema>';
        DBMS_XMLSCHEMA.registerSchema(schemaurl       => 'XSD_TEST.xsd', 
                                         schemadoc       => l_schema,
                                         local           => TRUE,
                                         gentypes        => FALSE,
                                         gentables       => FALSE,
                                         enablehierarchy => DBMS_XMLSCHEMA.enable_hierarchy_none); 
    END;
END;


And then I call my procedure:

BEGIN
    P_XML_DATE_TEST(2);
END;


After all that I try to validate the created xmltype data from my table against .xsd file that I have registred. I try to do it in two ways:

1.By using isSchemaValid

SELECT x.xml_file.isSchemaValid('XSD_TEST.xsd')
FROM XML_DATE_TEST x;


2.By using schemaValidate

BEGIN
    DECLARE 
        XML XMLTYPE;
    BEGIN
        select x.xml_file.createSchemaBasedXML('XSD_TEST.xsd')
        INTO XML 
        from XML_DATE_TEST X;

        xmltype.schemaValidate(XML);

    END;
END;


Problem:

With the first methode that I use(isSchemaValid) the result that I get is 1. That means that my xmltype data is correct against the provided .xsd xchema. With the second methode that I use(schemaValidate) the result that I get is error:

Error report -
ORA-30992: error occurred at Xpath /DocumentROOTTag/SomeDateTag
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at line 9
30992. 00000 -  "error occurred at Xpath %s"
*Cause:    
*Action:   See the following error and take appropriate action.


What I have tried:

When I remove the 'Z' part from the date format everything is ok but this is not a solution that is ok for me. The format of the date has to be like it is now.

and we said...

XML isn't a strong point of mine :-) but (I think) for a trailing "Z", you need to let the database know that you are dealing with a time zone

http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb05sto.htm

SQL> create table XML_DATE_TEST(
  2      xml_file xmltype
  3  );

Table created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE P_XML_DATE_TEST (p_testvar in number)
  2  IS
  3
  4      xml_help_variable xmltype;
  5
  6  BEGIN
  7
  8      SELECT XMLELEMENT
  9             ("DocumentROOTTag",
 10                 XMLATTRIBUTES(
 11                     'http://www.w3.org/2001/XMLSchema-instance' "xmlns:xsi"
 12                     , 'XSD_TEST.xsd' "xsi:noNamespaceSchemaLocation"),
 13             XMLELEMENT
 14             ("SomeDateTag",
 15             (to_char( sysdate,'yyyy-mm-dd')||'T'||to_char( sysdate,'hh24:mi:ss')||'Z'))
 16             )
 17      INTO xml_help_variable
 18      FROM dual
 19      WHERE p_testvar = 2;
 20
 21  INSERT INTO XML_DATE_TEST VALUES (xml_help_variable);
 22
 23  END P_XML_DATE_TEST;
 24  /

Procedure created.

SQL>
SQL> exec DBMS_XMLSCHEMA.deleteSchema(schemaurl       => 'XSD_TEST.xsd');

PL/SQL procedure successfully completed.

SQL>
SQL>     DECLARE
  2          l_schema CLOB;
  3      BEGIN
  4
  5
  6          l_schema := '<?xml version="1.0" encoding="UTF-8"?>
  7                      <!--W3C Schema generated by XMLSpy v2009 sp1 (http://www.altova.com)-->
  8                      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">   <<!!!!!!
  9                          <xs:element name="DocumentROOTTag">
 10                              <xs:complexType>
 11                                  <xs:sequence>
 12                                      <xs:element ref="SomeDateTag" />
 13                                  </xs:sequence>
 14                              </xs:complexType>
 15                          </xs:element>
 16                          <xs:element name="SomeDateTag"  xdb:SQLType="TIMESTAMP WITH TIME ZONE">   <<!!!!!!
 17                              <xs:simpleType>
 18                                  <xs:restriction base="xs:dateTime"/>
 19                              </xs:simpleType>
 20                          </xs:element>
 21                      </xs:schema>';
 22          DBMS_XMLSCHEMA.registerSchema(schemaurl       => 'XSD_TEST.xsd',
 23                                           schemadoc       => l_schema,
 24                                           local           => TRUE,
 25                                           gentypes        => FALSE,
 26                                           gentables       => FALSE,
 27                                           enablehierarchy => DBMS_XMLSCHEMA.enable_hierarchy_none);
 28      END;
 29  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2      P_XML_DATE_TEST(2);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT x.xml_file.isSchemaValid('XSD_TEST.xsd')
  2  FROM XML_DATE_TEST x;

X.XML_FILE.ISSCHEMAVALID('XSD_TEST.XSD')
----------------------------------------
                                       1

1 row selected.

SQL>
SQL> BEGIN
  2      DECLARE
  3          XML XMLTYPE;
  4      BEGIN
  5          select x.xml_file.createSchemaBasedXML('XSD_TEST.xsd')
  6          INTO XML
  7          from XML_DATE_TEST X;
  8
  9          xmltype.schemaValidate(XML);
 10
 11      END;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL>


More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.