Database Software

Hello All,

I am getting this error when validating my XML against an XSD using the Oracle function

XMTYPE variable.schemavalidate ();

ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm

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

I have the following XML generated which is having a date element with TimeZone stamped in it.

Piece from the XML data:

<TransactionFile CreationDate="2013-09-02T16:47:32+01:00" Entity="TEST">

I am validating this against an XSD and then applying a stylesheet to it and using Oracle XMLTRANSFORM

The stylesheet has the following code (XSLT code):

<CreDtTm><xsl:value-of select="@CreationDate" /></CreDtTm>

The XML is successfully validated and transformed into the final XML output, however for some reason it is adding fraction to my date as displayed below:

<CreDtTm>2013-09-05T16:40:44.000000+01:00</CreDtTm>

I am finally validating this output generated against a predefined standard XSD where the CredtTm is defined as a datetime variable

<xs:element name="CreDtTm" type="ISODateTime"/>

ISODateTime being datetime type

    <xs:simpleType name="ISODateTime">

        <xs:restriction base="xs:dateTime"/>

    </xs:simpleType>

Thanks for reading till here. Almost there the actual problem

I am validating this within my Oracle pakaged procedure, using the v_pain_xmldata.schemavalidate ();   (v_pain_xmldata   variable being an XMLTYPE variable )

When validating the XML against this XSD it is failing with the above mentioned error

Can anyone let me know why this is failing? Becuase as per the w3school's standard we can have the time zone format as per my final XML output

http://www.w3schools.com/schema/schema_dtypes_date.asp

I have tried hardcoing different dates within my final XML to see which element within the date is failing and below is the results. I think only when I add the plus minus hrs it is failing

2013-09-02T16:47:32+01:00  ---doesnt work
2013-09-02T16:47:32-01:00  ---doesnt work
2013-09-02 16:47:32         --doesnt work

2013-09-02                 --works
2013-09-02T16:47:32       --works
2013-09-02T16:47:32.000000  -- Works

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

FYI, I can not modify the XSD to add anything in it like this

<xs:attribute xdb:SQLType="TIMESTAMP WITH TIME ZONE" name="CreationDate" type="xs:dateTime" use="required" />

As this is a Standard SEPA XSD provided by the customer and hence no modifications can be done to it.

I only option for me is to modify the Stylesheet to probably enter a format to convert the date into the datetime format however, the above output generated is a valid datetime format.

Any question, please get back to me. Would appreciate any help on getting this problem sorted.

Thanks in advace.