How to insert an XML row in XML type column in SQL/XML using Oracle?

Question

I am trying to write an SQL/XML query in Oracle SQL Developer to update a certain row in the database table. The database has the following structure,

the attribute "Translations" is of XML type. For example i have the book with Title "Encore une fois" and OriginalLanguage "French" and ID "11". This book has 2 Editions as following 1:

ID "17", Year "1997", Price "120", Book "11"
Translations:  <Translations>
                  <Translation Language="English" Price="120"/>
                  <Translation Language="Russian" Price="110"/>
               </Translations>

the second edition is:

ID "18", Year "2001", Price "150", Book "11"
Translations:  <Translations>
                  <Translation Language="English" Publisher="Pels And Jafs" Price="180"/>
                  <Translation Language="Russian" Price="140"/>
               </Translations>

I want to insert a new "Translation" node in the second edition. I mean i want to add the row that says

<Translation Language="Norwegian" Publisher="KLC" Price="200"/>

I wrote the following Query

UPDATE BOOKDB.EDITION
SET Translations = XMLQUERY('copy $res := $t
                             modify insert node element Translation {attribute Language {"Norwegian"}, attribute Publisher {"KLC"}, attribute Price {200}} 
                             as last into $res/Translations
                             return $res'
                            PASSING Translations AS "t" RETURNING CONTENT)
WHERE Edition.Book in (SELECT ID FROM BOOKDB.Book WHERE Title = 'Encore une fois')

but i get 2 rows updated instead of one because it is placed in both editions which is wrong. How do i input the row in the second edition only?

Answer 1

It looks like you've gotten the hard part done already - you just need to add a condition to your UPDATE statement which specifies a single row in EDITION instead of two rows. I'd recommend testing your WHERE clause with a SELECT statement to make sure you're getting exactly the rows you want, e.g.

SELECT *
FROM BOOKDB.EDITION
WHERE Edition.Book in (SELECT ID FROM BOOKDB.Book WHERE Title = 'Encore une fois')

This returns the 2 rows you're currently updating, right?

So right after

WHERE Edition.Book in (SELECT ID FROM BOOKDB.Book WHERE Title = 'Encore une fois')

you could add

AND Edition.ID = '18'

, or you could add

AND Edition.Year = '2001'

If you want a more general-purpose solution to always update the most recent edition, you could do a subquery to make sure you're selecting the latest edition:

AND not exists (select 1 from BOOKDB.Edition e 
                where e.Book = Edition.Book 
                 and e.Year > Edition.Year)

Once you have a WHERE clause that picks the rows you want, add it back to your UPDATE statement:

UPDATE BOOKDB.EDITION
SET Translations = XMLQUERY('copy $res := $t
                         modify insert node element Translation {attribute Language {"Norwegian"}, attribute Publisher {"KLC"}, attribute Price {200}} 
                         as last into $res/Translations
                         return $res'
                        PASSING Translations AS "t" RETURNING CONTENT)
WHERE Edition.Book in (SELECT ID FROM BOOKDB.Book WHERE Title = 'Encore une fois')
  AND Edition.ID = '18'