Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need help with XML processing

515357Oct 13 2010 — edited Oct 14 2010
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 11 2010
Added on Oct 13 2010
6 comments
219 views