Search code examples
sqlxmloracle-databaseoracle-sqldeveloperoracle12c

How to edit XML column in SQL Developer?

I would like to edit XML column which is displaying as (XMLTYPE) by SQL Developer editor (I go there by clicking on the field twice, edit, then save).

After that the displayed value changes to sqldev.xml:/home/myuser/.sqldeveloper/tmp/XMLType8226206531089284015.xml

Build after save retrieving next build context...

Build after save building project 1 of 1 queued projects

Compiling...
  Ignoring /home/username/.sqldeveloper/tmp/XMLType5691884284875805681.xml; not on source path
[11:45:33 AM] Compilation complete: 0 errors, 1 warnings.

Build after save finished

and when I try to commit:

UPDATE "USERNAME"."TABLENAME" SET  WHERE ROWID = 'AABWNKAAEAAABSbAAB' AND ORA_ROWSCN = '6951979'

One error saving changes to table "USERNAME"."TABLENAME":
Row 1: Illegal format in column NEXTCOLUMN.

I tried to look for this error and found people who also had it, but without the solution. If you have an advice how to report it to Oracle, it will be also helpful.

Solution

  • Hope this will be of help to you:

    UPDATE table_name 
SET table_column=
   UPDATEXML(table_column,
   '/sampleTag1/sampleTag2/text()','value2')
   WHERE some_column = some value --<< this part is where you put your condition

    Here is where you can find more about it: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions205.htm

    -------------------------

    If your problem is with editing through SQL developer manually via integrated editor then it is, as far as my testing and researching can tell, because of the SQL Developer version.

    You have noted in your comment that you use version 4.1.x and I have found few places where people confirm that they had the same problem with this version.

    I also have 4.1.x version and I have also successfully repeated your error where the developer is referring to my .xml file in my ...\sqldeveloper\tmp folder not being on it's source path :

    Compiling... Ignoring C:\Users\trle\AppData\Roaming\SQL Developer\tmp\XMLType6413036461637067751.xml; not on source path [4:33:29 PM] Compilation complete: 0 errors, 1 warnings.

    I have then downloaded version 19.2.x where there is no such problem and all works just fine.

    So my answer to your problem is to download some newer version of SQL developer. In my case 19.2. works.

    -------------------------

    UPDATE Just tested on version 4.2.x - also works