java在使用XPath查询oracle数据库时，如何返回值列表而不是字符串？

Question

我正在使用XPath查询oracle数据库，其中我要查询的字段如下所示：

<!-- language: lang-xml -->
<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>

我想把在电影《教父》中出演的所有演员的名字还给他们。此时，我的代码看起来像：

result = stmt.executeQuery("SELECT a.FILM.extract('/film[title=\"Godfather, The\"]/cast/performer/actor/text()') "
                + "FROM ASS2_Film a "
                + "WHERE a.film.existsNode('/film[title=\"Godfather, The\"]')=1");

System.out.println("\nActor");            

while (result.next()) {   
    System.out.println(result.getString(1)+"\n");
}

在我的代码返回时：

Actor
Marlon BrandoAl PacinoDiane KeatonRobert DuvallJames Caan

如我所愿，返回为：

Actor
Marlon Brando
Al Pacino
Diane Keaton
Robert Duvall
James Caan

谢谢你的帮助

Answer 1

EXTRACT（和EXTRACTVALUE）是不推荐使用的函数。您应该使用XMLTABLE：

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  

（我加入role列只是为了获得更多信息；如果不需要查看，您只需从XMLTABLE部分的列列表中删除该列即可。）