This discussion is archived
12 Replies Latest reply: Sep 18, 2013 1:23 AM by Sanjiv RSS

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

Sanjiv Newbie
Currently Being Moderated

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.

  • 1. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    odie_63 Guru
    Currently Being Moderated

    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.

    The proper solution to this issue is to add the "TIMESTAMP WITH TIME ZONE" annotation.

    The following thread describes the same situation and provides an option :

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

  • 2. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    Sanjiv Newbie
    Currently Being Moderated

    Hi

     

    Will have to ask our DBA whether it will be possible to install that utilities on all our customers database as part of our s/w release upgrade. Only then I suppose I will be able to use the annotation during runtime.

    Meanwhile, I guess, I will have to add the XDB:SQLTYPE in that Standard XSD, which I wanted to use it as a last resort.

    Is there any way, we can specify a format within the XSL code to ensure that that elements gets printed in a certain way?

    I am new to XSL language and hence the questions.

     

    Thanks

  • 3. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    odie_63 Guru
    Currently Being Moderated

    Is there any way, we can specify a format within the XSL code to ensure that that elements gets printed in a certain way?

    Which format would you like to use ? Give an example of input/output.

     

    Thanks.

  • 4. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    Sanjiv Newbie
    Currently Being Moderated

    The XML input would be:

     

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

     

    the current code within XSL is to get the value of the attribute CreationDate:

     

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

     

    The expected output is

     

    <CreDtTm>2013-09-02T16:47:32.000000+01:00</CreDtTm>

     

     


  • 5. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    odie_63 Guru
    Currently Being Moderated

    The expected output is

     

    <CreDtTm>2013-09-02T16:47:32.000000+01:00</CreDtTm>


    Are you sure ?

    It won't work if you keep the timezone information (unless you introduce the annotation).


    Do you want to keep the timezone in the final output?


  • 6. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    Sanjiv Newbie
    Currently Being Moderated

    The thing is that I need to produce this output in the final XML using the XSD that does not have that annotation

     

    I have tried validating this output with the timezone within it against the same XSD which does not have any annotation, online via some free formatter tool available.

     

    http://www.freeformatter.com/xml-validator-xsd.html

     

    I input my XML and XSD on this site and it validates the XML successfully.

    However, validating the same in Oracle using Schemavalidate() gives this Oracle error.

     

    Hence I want the timezone in the output XML, but ideally not change the XSD giving any annotation as this XSD is used by all the banks all across europe.

    Since the XML is validated successfully through some non-oracle validator, I guess I will have not choice but to add the annotation in this XSD and hope that the XML output when given to the banks, they may use some non-oracle validator to validate the same XML.

    I have to do the same validation but using Oracle and it is failing with this error only within oracle and hence was looking ways to overcome this problem without modifying the XSD.

  • 7. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    odie_63 Guru
    Currently Being Moderated

    It seems to work if the schema is registered with binary XML option :

    SQL> ALTER SESSION SET nls_numeric_characters = '.,';

     

    Session altered

     

    SQL>

    SQL> BEGIN

      2

      3    dbms_xmlschema.registerSchema(

      4      schemaURL => 'test_ts.xsd'

      5    , schemaDoc =>

      6

      7    '<?xml version="1.0" encoding="utf-8"?>

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

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

    10    <xs:simpleType name="ISODateTime">

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

    12    </xs:simpleType>

    13  </xs:schema>'

    14

    15    , local => true

    16    , genTypes => false

    17    , genTables => false

    18    , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE

    19    , options => dbms_xmlschema.REGISTER_BINARYXML

    20    );

    21

    22  END;

    23  /

     

    PL/SQL procedure successfully completed

     

    SQL>

    SQL> DECLARE

      2    doc xmltype := xmltype('<CreDtTm>2012-06-02T09:30:47.000000+01:00</CreDtTm>', 'test_ts.xsd');

      3  BEGIN

      4    doc.schemaValidate();

      5  END;

      6  /

     

    PL/SQL procedure successfully completed

     

  • 8. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    Sanjiv Newbie
    Currently Being Moderated

    I had tried in my package to alter the session parameter during runtime as mentioned by you, but it didnt work.

    I will try this when registering the schema. Thanks so far for replying so promptly.

    Have a nice weekend. Will reply to you on monday as to what path I chosed finally.

     


  • 9. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    odie_63 Guru
    Currently Being Moderated

    I did not say the ALTER SESSION would solve the problem.

    The ALTER SESSION is just there because my session's NLS default to french settings, in particular the comma (,) is used for decimal separator, which causes problem when the timestamp contains a fractional part.

    That has been discussed here if you're interested :

    Validate XML: DateTime -> ORA-01830 / 01858

     

    You may not have to alter your session at all if yours uses ".," by default.

     

    Of course, the important point to retain from my previous example is that you have to delete the schema and register it again with binary XML option.

  • 10. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    Sanjiv Newbie
    Currently Being Moderated

    Hi there,

    I eventually decided to add the xmlns:xdb="http://xmlns.oracle.com/xdb"  in the XSD to overcome this problem.

     

    However, now I am getting the below mentioned error:

     

    Others ORA-31011: XML parsing failed

    ORA-19202: Error occurred in XML processing

    LPX-00601: Invalid token in: 'Transactions/Transaction/BusinessType='B2B')'

     

    The section of XSLT code. Basically, I am trying to add an if else condition in my XSLT code,

     

             <LclInstrm>

              <xsl:choose>

               <xsl:when test="Transactions/Transaction/BusinessType=''B2B'')">

                <Cd><xsl:value-of select="Transactions/Transaction/BusinessType"/></Cd>

               </xsl:when>

               <xsl:otherwise><Cd>CORE</Cd></xsl:otherwise>

              </xsl:choose>

             </LclInstrm>

             <SeqTp><xsl:value-of select="Transactions/Transaction/SequenceType"/></SeqTp>

     

    And when Transforming it as mentioned below it is failing with above mentioned error

     

            Select xmltransform(v_xmldata, XMLTYPE (v_stylesheet)).GETCLOBVAL()

            INTO po_xmldata

            FROM DUAL ;

     

    I have got the following data (section from my XML data) which I am putting in in v_xmldata XMLTYPE variable.

     

    - <Transactions TotalAmount="120" TransactionCount="1" ControlTotal="43324">

    - <Transaction>

    - <TargetCompany>

      <Name>SMA (leeds) Limited</Name>

    - <Address>

      <StreetName>Pontefract Lane</StreetName>

      <ZIPCode>LS9 0PS</ZIPCode>

      </Address>

    - <BankAccount>

      <IBAN>FR324432432423432</IBAN>

      <BIC>3254353435</BIC>

      </BankAccount>

      <BankAccountNumber>123</BankAccountNumber>

      <VATNumber />

      </TargetCompany>

      <BusinessType>B2B</BusinessType>

      <MandateIdentification>43324</MandateIdentification>

      <SequenceType>RCUR</SequenceType>

     

    Its the element name BusinessType I am trying to read and put a condition around it in my XSLT code.

     

     

     

  • 11. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    odie_63 Guru
    Currently Being Moderated

    What's this parenthesis for ?

    <xsl:when test="Transactions/Transaction/BusinessType=''B2B'')">
  • 12. Re: ORA-30992: error occurred at Xpath /Document/CstmrCdtTrfInitn/GrpHdr/CreDtTm
    Sanjiv Newbie
    Currently Being Moderated

    Silly me.... Thanks so much. Some typo when putting it in the script file I think.

    After removing the bracket the choose condition worked fine.

Legend

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