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 -

Thomas Kirchhoff · Accepted Answer · 2021-01-25 15:32:09Z

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 ));

answered Jan 25, 2021 at 15:32

Thomas Kirchhoff

1,1181 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update XML attribute in clob Oracle using XMLQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related