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>'

score 0 · Answer 1 · answered Jul 17 '19 at 12:21

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.

How to update oracle XML datatype in table through Xquery

1 Answers1