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?

kfinity · Accepted Answer · 2019-05-09 12:27:47Z

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'

Ponder Stibbons · Accepted Answer · 2019-05-09 12:49:26Z

If you want to update only second edition then write subquery which finds second id and use this subquery in your in clause. I used id for ordering, but you can also use year. If you want year use it in order by in rownumber.

UPDATE 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 (book, id) in (
    select book, id 
      from (
        select book.id book, edition.id, 
               row_number() over (partition by book.id order by edition.id) rn 
          from book join edition on book.id = edition.book where title = 'Encore une fois')
      where rn = 2);

^{dbfiddle demo}

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related