Hallo
I hope you can give me an advice... but first the data:
Here is a description of relevant tables:
book (id, title, author_id, year)
author (author_id, name)
I´ve created a view of xmltype with the following output:
...
<Books year="2003">
<Book>
<Title>The Great Gatsby</Title>
<Author Id="564">F. Scott Fitzgerald</Author>
</Book>
<Book>
<Title>To Kill a Mockingbird</Title>
<Author Id="23">Harper Lee</Author>
</Book>
</Books>
<Books year="2004">
<Book>
<Title>Catch 22</Title>
<Author Id="401">Joseph Heller</Author>
</Book>
</Books>
Both elements books and book are maxOccurs=unbounded. The relevant piece of code in the view looks like:
....
select xmlagg(xmlelement("Books", xmlattributes(virtual.y as "year")
, (select xmlagg(xmlelement(
"book",xmlelement("Title",book.title),xmlelement("Author",xmlattributes(author.author_id as "Id"),author.name))
) from book, author where book.author_id=author.author_id and book.year=virtual.y
) order by virtual.y
) from (select distinct(year) as y from book) virtual
......
Here are some lines of code of my trigger (instead of insert...):
for books_cur in (
select extractvalue(value(x),'/Books/@year') as year
from table (xmlsequence(extract(:new.object_value,'/MyBooks/Books'))) x
) loop
for book_rec in (
select extractvalue(value(z),'/Book/Title') as title,
extractvalue(value(z),'/Book/Author') as author,
extractvalue(value(z),'/Book/Author/@Id') as id
from table(xmlsequence(extract(:new.object_value,'/MyBooks/Books[@year='||books_cur.year||']/book'))) z
) loop
begin
insert into author values (book_rec.id, book_rec.author);
exception when others then null;
end;
begin
insert into book values (book_sequence.nextVal, book_rec.title, book_rec.id, books_rec.year);
exception when others then null;
end;
end loop;
end loop;
I have two questions about the trigger:
1. Is it possible to reference the table x from the outer loop in the inner loop? I tried this, but it doesn't work:
" ... from table(xmlsequence(extract(x.object_value,'/Books/book'))) z ..."
2. I put the two insert statements into separate blocks because the new book should be inserted even than the author still exists (and raises an exception). Is there a better solution keeping an eye on performance?
thank you,
Gordon