I have a table in oracle
create table MOVIE
(MOVIE_ID VARCHAR2(10 BYTE),
TITLE VARCHAR2(50 BYTE),
DIRECTOR VARCHAR2(50 BYTE),
WRITER VARCHAR2(50 BYTE),
GENRE VARCHAR2(50 BYTE),
RELEASE_DATE DATE,
RUNTIME NUMBER,
REVIEWS XMLTYPE);
REVIEW IS OF XML TYPE
Each Reviews has multiple tags of REVIEW(MORE THAN ONE TAG WITH A TAG)
I Have 25 such Reviews with multiple Review Tag inside each of them
I want the ids of repeating tag in different rows
I have written the following query
SELECT
XMLQuery(
'for $i in distinct-values (/Reviews/REVIEW/ID)
return data($i)'
PASSING REVIEWS RETURNING CONTENT) ID
FROM MOVIE;
The above query is giving me the output as M17 M18 M19 if there are inside the same REVIEW tag.
I am getting all the three in a single row I want them in 3 different rows.
M17
M18
M19
Any idea whats wrong with the query.
XMLTable() join with MOVIE
table must work for 11g :
select
m.movie_id,
id_list.review_id
from
movie m,
XMLTable(
'for $i in distinct-values($param/Reviews/REVIEW/ID) return $i'
passing m.reviews as "param"
columns review_id varchar2(4000) path '//ID'
) id_list
But in case of 10g it goes harder, because such construction always get value for REVIEWS
field from first selected record.
Tricks with multisets won't work and produce same results (got values from first record).
I found only solution for 10g by aggregating all results by XMLAgg() into one record and then build new dataset from that record with XMLTable().
select
movie_id,
review_id
from
XMLTable(
'
for $movie in $big_root/full_list/movie_entry,
$review_id in $movie/ID
return <row><movie_id>{data($movie/@movie_id)}</movie_id><review_id>{$id}</review_id></row>
'
passing
(
select
XMLElement("full_list",
XMLAgg(
XMLElement("movie_entry",
XMLAttributes(m.movie_id as "movie_id"),
XMLQuery(
'for $i in distinct-values($param/Reviews/REVIEW/ID) return $i'
passing by value m.reviews as "param"
returning content
)
)
)
)
from
movie m
)
as "big_root"
columns
movie_id varchar2(4000) path '//row/movie_id',
review_id varchar2(4000) path '//row/review_id'
)
Sorry, I can't test now this query but hope it's enough to give you idea about what it looks like.
P.S. If anyone can explain such behavior of Oracle 10g in case of joining with XMLTable() constructed from field it would be nice :)