Skip to main content

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/c178c@m=83] and $t/row/c178c@m=82]='USA' then replace value of node $t/row/c178c@m=83] with ("NEW YORK") else() ,

if fn:exists /row/c4 and $t/row/c178c@m=82]='USA' then replace value of node $t/row/c4 with ("Manhattan")else() ,

if fn:exists /row/c5 and $t/row/c178c@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/c178r@m=83] and $t/row/c178r@m=82]='USA' then replace value of node $t/row/c178r@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/c178s@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"

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",) else(),

if fn:exists /row/c3 then replace value of node $t/row/c3 with concat("lastname",) 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 ("BC") 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

);


Reply