How Do I Return A List Of Values Instead Of A String When Querying A Oracle Database Using Xpath?

I am using XPath to query an oracle database where the field I am querying looks like:

Solution 1:

EXTRACT (and EXTRACTVALUE) are deprecated functions. You should use XMLTABLE instead:

with sample_data as (select xmltype('<film><title>Godfather, The</title><year>1972</year><directors><director>Francis Ford Coppola</director></directors><genres><genre>Crime</genre><genre>Drama</genre></genres><plot>Son of a mafia boss takes over when his father is critically wounded in a mob hit.</plot><cast><performer><actor>Marlon Brando</actor><role>Don Vito Corleone</role></performer><performer><actor>Al Pacino</actor><role>Michael Corleone</role></performer><performer><actor>Diane Keaton</actor><role>Kay Adams Corleone</role></performer><performer><actor>Robert Duvall</actor><role>Tom Hagen</role></performer><performer><actor>James Caan</actor><role>Sonny Corleone</role></performer></cast></film>') x from dual)
select x.*
from   sample_data sd,
       xmltable('/film[title="Godfather, The"]/cast/performer' passing sd.x
                columns actor varchar2(50) path '//actor',
                        role varchar2(50) path '//role') x;

ACTOR                                              ROLE                                              
-------------------------------------------------- --------------------------------------------------
Marlon Brando                                      Don Vito Corleone                                 
Al Pacino                                          Michael Corleone                                  
Diane Keaton                                       Kay Adams Corleone                                
Robert Duvall                                      Tom Hagen                                         
James Caan                                         Sonny Corleone  

(I've included the role column just for additional info; you would just remove that column from the column list in the XMLTABLE part if you don't need to see it.)