xmloracle11gqxmlqueryxmlupdate

updating xml tables in oracle (use case - masking sensitive data after refresh )

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 if its "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
            );


ORA-19114: XPST0003 - error during parsing the XQuery expression: 
LPX-00801: XQuery syntax error at 'and'
5   if
(fn:exists(/row/c178[@m=83])) and $t/row/c178[@m=82]="USA" then replace 
-                                    ^

ORA-06512: at line 6

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"

Solution

  • 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 ("NEY 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

    );