I have the following XML structure. I am trying to get the results as shown below in my example.
with a as ( select '<?xml version="1.0" encoding="utf-8" ?>
<GetTheatersAndMoviesResponse>
<GetTheatersAndMoviesResult>
<Theater>
<Name>Hollywood Studio Theatre</Name>
<Address>5996 Westerville Road, Westerville, OH</Address>
<Movies>
<Movie>
<Rating>PG</Rating>
<Name>Alpha and Omega</Name>
</Movie>
<Movie>
<Rating>PG13</Rating>
<Name>Devil</Name>
</Movie>
</Movies>
</Theater>
<Theater>
<Name>Rave Motion Pictures Polaris 18</Name>
<Address>1071 Gemini Place, Columbus, OH</Address>
<Movies>
<Movie>
<Rating>PG-13</Rating>
<Name>Life As We Know It</Name>
</Movie>
<Movie>
<Rating>R</Rating>
<Name>Case 39</Name>
</Movie>
<Movie>
<Rating>PG</Rating>
<Name>Secretariat</Name>
</Movie>
</Movies>
</Theater>
</GetTheatersAndMoviesResult>
</GetTheatersAndMoviesResponse>' as clob001 from dual
)
select extractValue(value(t), '/Theater/Name') "Theater",
extractValue(value(t), '/Theater/Address') "Address"
from a c,
table(xmlsequence(extract(xmltype.createxml(c.clob001),'//GetTheatersAndMoviesResponse/GetTheatersAndMoviesResult/Theater'))) t
This produces following result.
Theater Address
=============================================================
Hollywood Studio Theatre 5996 Westerville Road, Westerville, OH
Rave Motion Pictures Polaris 18 1071 Gemini Place, Columbus, OH
If I replace the "select" statement as shown below
select extractValue(value(t), '/*/Name') "Name",
extractValue(value(t), '/*/Rating') "Rating"
from a c,
table(xmlsequence(extract(xmltype.createxml(c.clob001),'//GetTheatersAndMoviesResponse/GetTheatersAndMoviesResult/Theater/Movies/Movie'))) t
then I get the following:
Name Rating
==================================
Alpha and Omega PG
Devil PG13
Life As We Know It PG-13
Case 39 R
Secretariat PG
I would like to combine both, and get Theater, Show Name and Show Rating all in single row.
Theater Name Rating
=============================================================
Hollywood Studio Theatre Alpha and Omega PG
Hollywood Studio Theatre Devil PG13
Rave Motion Pictures Polaris 18 Life As We Know It PG-13
Rave Motion Pictures Polaris 18 Case 39 R
Rave Motion Pictures Polaris 18 Secretariat PG