使用XPath查询oracle数据库时，如何返回值列表而不是字符串? [英] How do I return a list of values instead of a string when querying a oracle database using XPath?

问题描述

我正在使用XPath来查询一个oracle数据库，其中我要查询的字段如下:

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

<!-- 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>

我想归还曾在电影中饰演过教父的所有演员.此刻，我的代码如下:

I want to return the all the actors who have starred in the film the godfather. At the minute my code looks like:

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");
}

在我的代码返回的那一刻:

At the minute my code is returning:

Actor
Marlon BrandoAl PacinoDiane KeatonRobert DuvallJames Caan

在我希望的地方返回为:

Where as I want it returned as:

Actor
Marlon Brando
Al Pacino
Diane Keaton
Robert Duvall
James Caan

感谢您的帮助

推荐答案

EXTRACT(和EXTRACTVALUE)是已弃用的函数.您应该改用XMLTABLE:

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  

(我提供的角色列只是为了提供其他信息；如果不需要，您可以从XMLTABLE部分的列列表中删除该列.)

(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.)

这篇关于使用XPath查询oracle数据库时，如何返回值列表而不是字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！