I am having extracted short description which is trimmed to 200 characters from the xml payload. I need to replace the xml with trimmed short description. Framed with below , only i can replace test , but not the exact attribute
WITH testdata(xmlfile) AS(select xmltype(message) , substr(extract(xmltype(message), '/xxx/xxx/xxx/@xxx'),1,200) as Short
from table where state='Initial' and flow_name='xxxx' and
sub_flow_name='xxxx' and state='Initial' and errortxnid > '20220522' and errormessage like '%ORA-12899: value too large for column%')
SELECT XMLQUERY
( 'copy $tmp := . modify replace value of node $tmp/xxx/xxx/xxx/@xxx with "test" return $tmp 'PASSING xmlfile
RETURNING CONTENT)FROM testdata