12 Replies Latest reply: Sep 18, 2013 3:23 AM by Sanjiv RSS

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

    Sanjiv

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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.