This discussion is archived
3 Replies Latest reply: Feb 19, 2013 9:56 AM by odie_63 RSS

insertchildxml failed, LSX-00234: invalid decimal

user4423142 Newbie
Currently Being Moderated
Hello,

My problem is to insert a new element into a XML document. I get the error LSX-00234: invalid decimal "2100,5", and this decimal value is the value of the first value inserted the first time, not the new value.
Here is the XML schema :

Oracle 11g release 11.2.0.3.0 Red Hat Enterprise Server 5.7

declare xml_data CLOB;
begin xml_data:='<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmnls.oracle.com/xdb"
version="1.0" xdb:storeVarrayAsTable="true">
<xs:element name="cameras" type="appareils"/>
<xs:complexType name="appareils" xdb:SQLTYPE="APPAREILS_T">
<xs:sequence>
<xs:element name="appareil" minOccurs="0" maxOccurs="unbounded" type="appareil"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="appareil" xdb:SQLTYPE="APPAREIL_T">
<xs:sequence>
<xs:element name="marque" type="xs:string" xdb:SQLTYPE="MARQUE"/>
<xs:element name="modele" type="xs:string" xdb:SQLTYPE="MODELE"/>
<xs:element name="prix" type="prix_type" xdb:SQLTYPE="PRIX"/>
</xs:sequence>
</xs:complexType>
<xs:simpleType name="prix_type">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="2"/>
<xs:totalDigits value="12"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>';
DBMS_XMLSCHEMA.REGISTERSCHEMA(schemaurl => 'cameras.xsd',
schemadoc => xml_data,local => true, gentypes => false, gentables => false,
OPTIONS => DBMS_XMLSCHEMA.REGISTER_BINARYXML);
end;

To create the table :

CREATE TABLE CAMERAS OF XMLTYPE
XMLTYPE STORE AS BINARY XML
XMLSCHEMA "cameras.xsd" element "cameras"
DISALLOW NONSCHEMA;

To populate this table with two entries :

declare xml_data CLOB;
begin xml_data:='<?xml version="1.0" encoding="UTF-8" ?>
<cameras>
<appareil>
<marque>CANON</marque>
<modele>EOS 6D</modele>
<prix>2100.50</prix>
</appareil>
<appareil>
<marque>NIKON</marque>
<modele>D600</modele>
<prix>1900.50</prix>
</appareil>
</cameras>';
insert into cameras values (XMLType(xml_data));
end;

Now I want to insert a new element with the statement :

update cameras set object_value = insertchildxml(object_value,
'/cameras', 'appareil',
xmltype(' <appareil>
<marque>CANON</marque>
<modele>EF-S</modele>
<prix>550.25</prix>
</appareil>'));

I get the following error :

Erreur SQL : ORA-31154: document XML non valide
ORA-19202: Une erreur s'est produite lors du traitement la fonction XML (
LSX-00234: invalid decimal "2100,5"
)
31154. 00000 - "invalid XML document"
*Cause:    The XML document is invalid with respect to its XML Schema.

Thanks in advance for your help
  • 1. Re: insertchildxml failed, LSX-00234: invalid decimal
    odie_63 Guru
    Currently Being Moderated
    Hi, welcome to the forum.

    For XML DB related questions, it's better to post in the dedicated forum : {forum:id=34}

    Thanks for the detailed test case, it's very helpful. You can use the &#x7B;code} tags to preserve formatting.
    I'm working with pretty much the same environment (11.2.0.3, french NLS settings), and I can reproduce the scenario :
    SQL> update cameras
      2  set object_value =
      3      insertchildxml(
      4        object_value
      5      , '/cameras'
      6      , 'appareil'
      7      , xmltype(' <appareil>
      8  <marque>CANON</marque>
      9  <modele>EF-S</modele>
     10  <prix>550.25</prix>
     11  </appareil>')
     12      ) ;
        , xmltype(' <appareil>
          *
    ERROR at line 7:
    ORA-31154: document XML non valide
    ORA-19202: Une erreur s'est produite lors du traitement la fonction XML (
    LSX-00234: invalid decimal "2100,5"
    )
    Oracle seems to rely on the session-level NLS settings when dealing with number datatypes.
    Personally, I consider this behaviour as a bug since XML DB is now supposed to be fully W3C-compliant.

    The workaround is to set the NLS_NUMERIC_CHARACTERS parameter for the session so that the correct decimal separator is used during XML processing :
    SQL> alter session set nls_numeric_characters = ".,";
    
    Session altered.
    
    SQL> update cameras
      2  set object_value =
      3      insertchildxml(
      4        object_value
      5      , '/cameras'
      6      , 'appareil'
      7      , xmltype(' <appareil>
      8  <marque>CANON</marque>
      9  <modele>EF-S</modele>
     10  <prix>550.25</prix>
     11  </appareil>')
     12      ) ;
    
    1 row updated.
    Note that insertChildXML function and alike are deprecated in 11.2.0.3.
    The recommended method is now XQuery Update :
    SQL> update cameras
      2  set object_value =
      3      xmlquery(
      4        'copy $d := .
      5         modify (
      6           insert node $new_app into $d/cameras
      7         )
      8         return $d'
      9        passing object_value
     10              , xmlparse(content '<appareil>
     11  <marque>CANON</marque>
     12  <modele>EF-S</modele>
     13  <prix>550.25</prix>
     14  </appareil>') as "new_app"
     15        returning content
     16      )
     17  ;
    
    1 row updated.
    
    SQL> set long 5000
    SQL> set pages 100
    SQL> select xmlserialize(document object_value) from cameras;
    
    XMLSERIALIZE(DOCUMENTOBJECT_VALUE)
    --------------------------------------------------------------------------------
    <?xml version="1.0" encoding="WINDOWS-1252"?>
    <cameras>
      <appareil>
        <marque>CANON</marque>
        <modele>EOS 6D</modele>
        <prix>2100.5</prix>
      </appareil>
      <appareil>
        <marque>NIKON</marque>
        <modele>D600</modele>
        <prix>1900.5</prix>
      </appareil>
      <appareil>
        <marque>CANON</marque>
        <modele>EF-S</modele>
        <prix>550.25</prix>
      </appareil>
    </cameras>
    For more details see : http://www.oracle.com/technetwork/database-features/xmldb/xmlqueryoptimize11gr2-168036.pdf
  • 2. Re: insertchildxml failed, LSX-00234: invalid decimal
    user4423142 Newbie
    Currently Being Moderated
    Merci beaucoup pour cette réponse ultra rapide et précise.

    Je cherchais depuis pas mal de temps de l'information sur le Net, l'auteur d'un livre m'avait parlé du forum Oracle, j'ai bien fait de poster ma demande.
    Sachez sincèrement que votre intervention m'a vraiment impressionnée tant par sa qualité que par sa rapidité, chose peu courante.

    Vraiment merci encore, je vais étudier le document attaché au lien, l'Oracle XML DB Best Practices.
  • 3. Re: insertchildxml failed, LSX-00234: invalid decimal
    odie_63 Guru
    Currently Being Moderated
    Pas de problème, merci à vous également.
    C'est d'autant plus agréable et aisé de répondre quand on dispose de manière claire de toutes les informations dès le départ.

    Marc.

Legend

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