How to update oracle XML datatype in table through Xquery

Question

While updating oracle xml data in table am getting below error.

SQL Error: ORA-18126: XUDY0027 - Invalid target expression 18126. 00000 - "XUDY0027 - Invalid target expression" *Cause: Target expression of an INSERT, REPLACE, or RENAME expression evaluated to an empty sequence. *Action: None.

update test
set XMLDATA=
           xmlquery(
             'copy $d := .
             modify (
                for $i in $d/workbook/worksheet
                return replace value of node $i/@sheetName with concat($i/@sheetName, $i/@sheetId)
            )
              return $d'
            passing test.XMLDATA
            returning content
         )

XML:-

'<workbook>
 <worksheet sheetName="MySheet" sheetId="1"/>
</workbook>'

Answer 1

If you target the attribute rather than its parent node then you can update that value directly; you just need to look at the parent for the name/ID values to concatenate:

update test
set XMLDATA=
           xmlquery(
             'copy $d := .
             modify (
                for $i in $d/workbook/worksheet/@sheetName
                return replace value of node $i with concat($i/../@sheetName, $i/../@sheetId)
            )
              return $d'
            passing test.XMLDATA
            returning content
         )

db<>fiddle

Note that this throws an ORA-600 for me on 11.2.0.4; on db<>fiddle (11.2.0.2) it gets a slightly less nasty "ORA-19112: error raised during evaluation: XQuery Update connot be compiled". I don't have a 12c DB I can test this against right now, so it's feasible it will error on 12cR1 or 12cR2 or both - you havent' said which of those you are using, or your patch level. But as it was fixed some time before 18c you might be OK.