i am trying to update xml column in oracle and getting errors which i am not able to resolve . need some help here
if the data exists there and $t/row/c178[@m=82]='USA' then only update the value or do nothing
update USER.TABLE t1
set t1.xmlrecord=
XMLQuery('
copy $t := . modify (
if fn:exists /row/c2 then replace value of node $t/row/c2 with concat("firstname",$t/row/@id) else(),
if fn:exists /row/c3 then replace value of node $t/row/c3 with concat("lastname",$t/row/@id) else(),
if fn:exists /row/c178[@m=83] and $t/row/c178[@m=82]='USA' then replace value of node $t/row/c178[@m=83] with ("NEW YORK") else() ,
if fn:exists /row/c4 and $t/row/c178[@m=82]='USA' then replace value of node $t/row/c4 with ("Manhattan")else() ,
if fn:exists /row/c5 and $t/row/c178[@m=82]='USA' then replace value of node $t/row/c5 with concat ("NY 10036, USA.",$t/row/@id) else(),
)
return $t
'
passing t1.xmlrecord
returning content
);
if fn:exists /row/c178[@m=83] and $t/row/c178[@m=82]='USA' then replace value of node $t/row/c178[@m=83] with ("NEW YORK") else() ,
*
ERROR at line 12:
ORA-06550: line 12, column 55:
PL/SQL: ORA-19109: RETURNING keyword expected
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored
i am not getting these 2 conditions correct fn:exists /row/c178[@m=83] and $t/row/c178[@m=82]='USA'
this single condition is working fine --> (fn:exists(/row/c178[@m=83])) as i used it for some other tables but when we add this second condition everything is messed up --> $t/row/c178[@m=82]="USA"
