sql - How to update XML attribute in clob Oracle using XMLQuery

Question

Oracle table name: SR_DATA;

Table field name: XMLDATA type CLOB;

Field value:

<module xmlns="http://www.mytest.com/2008/FMSchema">
<tmEsObjective modelCodeScheme="A" modelCodeSchemeVersion="01" modelCodeValue="ES_A"></tmEsObjective>
</module>

I need to update the value of the attribute "modelCodeValue" into ES_B.

This is the code:

UPDATE SR_DATA
  SET XMLDATA =
    XMLQuery('copy $i := $p1 modify
                ((for $j in $i/module/tmEsObjective/@modelCodeValue
                  return replace value of node $j with $p2))  
                )
                return $i'
             PASSING XMLType(REPLACE(xmldata, 'xmlns="http://www.mytest.com/2008/FMSchema"', '')) AS "p1",
                     'ES_B' AS "p2"
             RETURNING CONTENT);

This code returns the error code: ORA-00932: inconsistent datatypes: expected CLOB got -

question from:https://stackoverflow.com/questions/65886704/how-to-update-xml-attribute-in-clob-oracle-using-xmlquery

Answer 1

Use getclobval() like this:

UPDATE SR_DATA
  SET XMLDATA = 
     XMLTYPE.GETCLOBVAL(XMLQuery('copy $i := $p1 modify
                            ((for $j in $i/module/tmEsObjective/@modelCodeValue
                              return replace value of node $j with $p2))  
                            
                            return $i'
                         PASSING XMLType(REPLACE(xmldata, 'xmlns="http://www.mytest.com/2008/FMSchema"', '')) AS "p1",
                                 'ES_B' AS "p2"
                         RETURNING CONTENT ));