6 Replies Latest reply on Dec 22, 2013 3:15 PM by odie_63

    INSERTCHILDXML fails with missing attribute and other weird behaviour

    user2176113

      Hi,

       

      I getting a strange error when executing the following statement:

       

      update test_table

      set object_value = insertchildxml(object_value, '/ns1:game/ns1:configurableFields','ns1:field',

      xmltype('<ns1:field xmlns:ns1="http://www.test.com/ppv/test/data" ns1:operation="New" ns1:modifiedOn="2013-12-12T15:24:06.349+05:30" ns1:modifiedBy="test" ns1:fieldName="field2"></ns1:field>'),

      'xmlns:ns1="http://www.test.com/ppv/test/data"')

       

      It returns:

       

      Error report:

      SQL Error: ORA-31154: invalid XML document

      ORA-19202: Error occurred in XML processing

      LSX-00266: missing required attribute "fieldName"

      31154. 00000 -  "invalid XML document"

      *Cause:    The XML document is invalid with respect to its XML Schema.

      *Action:   Fix the errors identified and try again.

       

      However, the following works:

       

      declare

        xml XMLType;

      begin 

           select insertchildxml(object_value, '/ns1:game/ns1:configurableFields','ns1:field', xmltype('<ns1:field xmlns:ns1="http://www.test.com/ppv/test/data" ns1:operation="New" ns1:modifiedOn="2013-12-12T15:24:06.349+05:30" ns1:modifiedBy="test"           ns1:fieldName="field2"></ns1:field>'), 'xmlns:ns1="http://www.test.com/ppv/test/data"') into xml

           from test_table ;

       

           update test_table set object_value = xml;

      end;

       

      Breaking the insertchildxml and the update into two steps works, however, as part of the same update statement it does not work.

       

      I'm using Oracle 11gR2. test_table is a table of XMLType. So far the update operations on this type seem to be buggy.

       

      Any help would be appreciated,

       

      Dale

        • 1. Re: INSERTCHILDXML fails with missing attribute and other weird behaviour
          odie_63

          I'm using Oracle 11gR2. test_table is a table of XMLType.

           

          Could you give :

          - the exact database version ?

          - the XML schema ?

           

          Are you using a schema-based binary XML or Object-Relational storage?

           

          Thanks.

          • 2. Re: INSERTCHILDXML fails with missing attribute and other weird behaviour
            user2176113

            I've included the information below.

             

            Thanks for taking a look,

             

            Dale

             

             

            DB Version = 11.2.0.1

             

                <xs:schema targetNamespace="http://www.test.com/ppv/test/data" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:bcsd="http://www.test.com/ppv/test/data" attributeFormDefault="qualified" elementFormDefault="qualified">

                    <xs:simpleType name="OperationType">

                        <xs:restriction base="xs:string">

                            <xs:enumeration value="Delete"/>

                            <xs:enumeration value="Edit"/>

                            <xs:enumeration value="New"/>

                            <xs:enumeration value="Copy"/>

                        </xs:restriction>

                    </xs:simpleType>

                    <xs:simpleType name="ActiveType">

                        <xs:restriction base="xs:string">

                            <xs:enumeration value="A"/>

                            <xs:enumeration value="I"/>

                        </xs:restriction>

                    </xs:simpleType>

                    <xs:complexType name="FieldType">

                        <xs:sequence/>

                        <xs:attribute name="fieldName" use="required" type="xs:string"/>

                        <xs:attribute name="modifiedBy" use="required" type="xs:string"/>

                        <xs:attribute name="modifiedOn" use="required" type="xs:dateTime"/>

                        <xs:attribute name="operation" use="required" type="bcsd:OperationType"/>

                        <xs:attribute name="value" use="required" type="xs:string"/>

                    </xs:complexType>

                    <xs:element name="game">

                        <xs:complexType>

                            <xs:sequence>

                                <xs:element name="awayTeam" type="xs:string" minOccurs="1" maxOccurs="1"/>

                                <xs:element name="configurableFields" type="bcsd:ConfigurableFieldsType" minOccurs="1" maxOccurs="1"/>

                                <xs:element name="gameDate" type="xs:date" minOccurs="1" maxOccurs="1"/>

                                <xs:element name="gameTime" type="xs:time" minOccurs="1" maxOccurs="1"/>

                                <xs:element name="gameType" type="xs:string" minOccurs="1" maxOccurs="1"/>

                                <xs:element name="homeTeam" type="xs:string" minOccurs="1" maxOccurs="1"/>

                                <xs:element name="publishedEvents" type="bcsd:PublishedEventsType" minOccurs="1" maxOccurs="1"/>

                                <xs:element name="recordSequence" type="xs:integer" minOccurs="1" maxOccurs="1" default="0"/>

                                <xs:element name="seasonYear" type="xs:gYear" minOccurs="1" maxOccurs="1"/>

                            </xs:sequence>

                            <xs:attribute name="active" use="required" type="bcsd:ActiveType"/>

                            <xs:attribute name="assetId" use="required" type="xs:integer"/>

                            <xs:attribute name="changeSequence" use="required" type="xs:integer"/>

                            <xs:attribute name="createdBy" use="required" type="xs:string"/>

                            <xs:attribute name="createdOn" use="required" type="xs:dateTime"/>

                            <xs:attribute name="fieldConfigurationId" use="required" type="xs:integer"/>

                            <xs:attribute name="modifiedBy" use="required" type="xs:string"/>

                            <xs:attribute name="modifiedOn" use="required" type="xs:dateTime"/>

                            <xs:attribute name="published" use="required" type="xs:boolean"/>

                        </xs:complexType>

                    </xs:element>

                    <xs:complexType name="ConfigurableFieldsType">

                        <xs:sequence>

                            <xs:element name="field" type="bcsd:FieldType" minOccurs="0" maxOccurs="unbounded"/>

                        </xs:sequence>

                    </xs:complexType>

                    <xs:complexType name="PublishedEventsType">

                        <xs:sequence>

                            <xs:element name="publishedEvent" type="bcsd:PublishedEventType" minOccurs="0" maxOccurs="unbounded"/>

                        </xs:sequence>

                    </xs:complexType>

                    <xs:complexType name="PublishedEventType">

                        <xs:sequence/>

                        <xs:attribute name="publishedDate" use="required" type="xs:dateTime"/>

                        <xs:attribute name="publishedSequence" use="required" type="xs:integer"/>

                    </xs:complexType>

                </xs:schema>

            • 3. Re: INSERTCHILDXML fails with missing attribute and other weird behaviour
              odie_63

              Thanks.

               

              You missed my last question :

              Are you using a schema-based binary XML or Object-Relational storage?

              • 4. Re: INSERTCHILDXML fails with missing attribute and other weird behaviour
                odie_63

                OK, I've been able to reproduce using a schema-based binary XMLType :

                 

                (tested on 11.2.0.2)

                SQL> update test_table

                  2  set object_value =

                  3      insertchildxml(

                  4        object_value

                  5      , '/ns1:game/ns1:configurableFields'

                  6      , 'ns1:field'

                  7      , xmlparse(document '<ns1:field xmlns:ns1="http://www.test.com/ppv/test/data"

                  8                                      ns1:value="ZZZ"

                  9                                      ns1:operation="New"

                10                                      ns1:modifiedOn="2013-12-12T15:24:06"

                11                                      ns1:modifiedBy="test"

                12                                      ns1:fieldName="field2"/>')

                13      , 'xmlns:ns1="http://www.test.com/ppv/test/data"'

                14      )

                15  ;

                update test_table

                       *

                ERROR at line 1:

                ORA-31154: invalid XML document

                ORA-19202: Error occurred in XML processing

                LSX-00266: missing required attribute "fieldName"

                 

                 

                Disabling XML DML rewrite works around the problem.

                I'll check on 11.2.0.3 and 12.1 to see if it's been fixed.

                SQL> update /*+ no_xml_dml_rewrite */ test_table

                  2  set object_value =

                  3      insertchildxml(

                  4        object_value

                  5      , '/ns1:game/ns1:configurableFields'

                  6      , 'ns1:field'

                  7      , xmlparse(document '<ns1:field xmlns:ns1="http://www.test.com/ppv/test/data"

                  8                                      ns1:value="ZZZ"

                  9                                      ns1:operation="New"

                10                                      ns1:modifiedOn="2013-12-12T15:24:06"

                11                                      ns1:modifiedBy="test"

                12                                      ns1:fieldName="field2"/>')

                13      , 'xmlns:ns1="http://www.test.com/ppv/test/data"'

                14      )

                15  ;

                 

                1 row updated.

                 

                • 5. Re: INSERTCHILDXML fails with missing attribute and other weird behaviour
                  user2176113

                  Thanks for your help with this.

                   

                  I've also discovered that putting the update statement into a package also does the trick.

                   

                  I came across another bug: I have a for loop that generates and inserts 2000 xml statements. Now and then I would get random insert failure - even though the xml passed an isValidSchema check - with an exception about an invalid character in the xml.

                   

                  For now I'm stuck with 11.2.0.1 since I want to sick with the same version as production.

                  • 6. Re: INSERTCHILDXML fails with missing attribute and other weird behaviour
                    odie_63

                    Not fixed on 11.2.0.3 :

                     

                    SQL> select * from v$version;

                     

                    BANNER

                    --------------------------------------------------------------------------------

                    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

                    PL/SQL Release 11.2.0.3.0 - Production

                    CORE    11.2.0.3.0      Production

                    TNS for 32-bit Windows: Version 11.2.0.3.0 - Production

                    NLSRTL Version 11.2.0.3.0 - Production

                     

                    SQL>

                    SQL>

                    SQL> update test_table

                      2  set object_value =

                      3      insertchildxml(

                      4        object_value

                      5      , '/ns1:game/ns1:configurableFields'

                      6      , 'ns1:field'

                      7      , xmlparse(document '<ns1:field xmlns:ns1="http://www.test.com/ppv/test/data"

                      8                                      ns1:value="ZZZ"

                      9                                      ns1:operation="New"

                    10                                     ns1:modifiedOn="2013-12-12T15:24:06"

                    11                                     ns1:modifiedBy="test"

                    12                                     ns1:fieldName="field2"/>')

                    13     , 'xmlns:ns1="http://www.test.com/ppv/test/data"'

                    14     )

                    15  ;

                    update test_table

                           *

                    ERROR at line 1:

                    ORA-31154: invalid XML document

                    ORA-19202: Error occurred in XML processing

                    LSX-00266: missing required attribute "fieldName"

                     

                     

                    SQL>

                    SQL>

                    SQL> update test_table

                      2  set object_value =

                      3      xmlquery(

                      4        'declare namespace ns1 = "http://www.test.com/ppv/test/data"; (: :)

                      5         copy $d := . modify (

                      6           (#ora:child-element-name ns1:field #){

                      7             insert node $field into $d/ns1:game/ns1:configurableFields

                      8           }

                      9         ) return $d'

                    10        passing object_value

                    11              , xmlparse(document '<ns1:field xmlns:ns1="http://www.test.com/ppv/test/data"

                    12                                              ns1:value="ZZZ"

                    13                                              ns1:operation="New"

                    14                                             ns1:modifiedOn="2013-12-12T15:24:06"

                    15                                             ns1:modifiedBy="test"

                    16                                             ns1:fieldName="field2"/>') as "field"

                    17        returning content

                    18     )

                    19  ;

                    update test_table

                           *

                    ERROR at line 1:

                    ORA-31154: invalid XML document

                    ORA-19202: Error occurred in XML processing

                    LSX-00266: missing required attribute "fieldName"