This discussion is archived
2 Replies Latest reply: Aug 6, 2013 5:18 AM by odie_63 RSS

XSD validation using xs:dateTime with time zone without xdb:SQLType

c811e4f2-e289-458a-859f-d237450480ed Newbie
Currently Being Moderated

I am working on some code which performs XSD validation against an XML document within an Oracle database. I have managed to get the validation code running but Oracle is complaining about how an xs:dateTime element is being supplied with the 'Z' at the end specifying it is UTC time. The error I am getting is;

 

ORA-30992: error occurred at Xpath [Xpath to xs:dateTime element]

ORA-01830: date format picture ends before converting entire input string

 

This is solved by me removing the 'Z' from the time, so making '2013-08-06T12:29:15Z' into '2013-08-06T12:29:15'. After spending a bit of time researching I was able to identify that if I included Oracles XDB namespace and specified the 'xdb:SQLType="TIMESTAMP WITH TIME ZONE"' for that element that this will perform the XSD validation correctly, allowing for the UTC marker at the end. Unfortunately for me this means that I would have to get an XSD which is maintained internationally over many organisations modified to include Oracle specific annotations, which will not be easy. I was wondering whether any one know's any way that I can specify through code that the timestamp must include the UTC marker, rather than having to get the XSD document changed

 

I know I could make these modifications to only the local copy of the XSD, but I would consider this a last resort as this would add an additional human step to any upgrades and we all know how prone to error us fleshy meat bags are.

 

Thanks for any help or advice anyone is able to give on this.

 

Sam

  • 1. Re: XSD validation using xs:dateTime with time zone without xdb:SQLType
    odie_63 Guru
    Currently Being Moderated
    I know I could make these modifications to only the local copy of the XSD, but I would consider this a last resort as this would add an additional human step to any upgrades and we all know how prone to error us fleshy meat bags are.

    It would not necessarily require someone (human) to make the changes.

    For example, you could imagine including the modification as part of the standard installation process and use something like this to do the job :

     

    SQL> declare

      2 

      3    my_schema xmltype := xmltype(

      4  '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

      5        <xs:complexType name="mainComplexType">

      6          <xs:sequence>

      7            <xs:element name="SomeString" type="xs:string" />

      8            <xs:element name="SomeDatetime" type="xs:dateTime" />

      9          </xs:sequence>

    10        </xs:complexType>

    11        <xs:element name="root" type="mainComplexType"/>

    12       </xs:schema>'

    13       ) ;

    14 

    15    result    clob;

    16 

    17  begin

    18 

    19    dbms_xmlschema_annotate.setTimeStampWithTimeZone(my_schema);

    20 

    21    select xmlserialize(document my_schema indent) into result from dual;

    22    dbms_output.put_line(result);

    23 

    24  end;

    25  /

     

    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">

      <xs:complexType name="mainComplexType">

        <xs:sequence>

          <xs:element name="SomeString" type="xs:string"/>

          <xs:element name="SomeDatetime" type="xs:dateTime" xdb:SQLType="TIMESTAMP WITH TIME ZONE"/>

        </xs:sequence>

      </xs:complexType>

      <xs:element name="root" type="mainComplexType"/>

    </xs:schema>

     

     

    PL/SQL procedure successfully completed

     

     

    DBMS_XMLSCHEMA_ANNOTATE is part of a separate set of tools we have to install on the db.

    The whole package is available here : http://download.oracle.com/otn/samplecode/xdb_util.zip

  • 2. Re: XSD validation using xs:dateTime with time zone without xdb:SQLType
    Marco Gralike Oracle ACE Director
    Currently Being Moderated

    Just for info...dbms_xmlschema_annotate...is now a standard package in the database from Oracle 12cR1 and upwards ---> DBMS_XMLSCHEMA_ANNOTATE

Legend

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