我正在使用 oracle 11g r2 数据库，基本上需要能够从中解析和选择一些节点。我花了几个小时在网上搜索并阅读 oracle xml db 手册，试图为我的问题找到合适的解决方案，但我似乎无法确定正确的方法。我有相当多的编程经验，但通常没有使用 xml、sql 或 oracle 数据库的经验，所以如果这是一个微不足道的问题，请原谅我。

好的，继续这个问题:

我有一个非常简单的 XML 文件，保存为 catalog.xml，如下所示:

<catalog>
    <cd>
        <title>Hide your heart</title>
        <artist>Bonnie Tyler</artist>
        <country>UK</country>
        <company>CBS Records</company>
        <price>9.90</price>
        <year>1988</year>
    </cd>
    <cd>
        <title>Empire Burlesque</title>
        <artist>Bob Dylan</artist>
        <country>USA</country>
        <company>Columbia</company>
        <price>10.90</price>
        <year>1985</year>
    </cd>
</catalog>

现在我希望能够提取给定某位艺术家的 CD 的标题。例如，如果艺术家是“鲍勃迪伦”，那么标题应该是“帝国滑稽表演”

现在我在Oracle中创建了一个XMLType表如下:

CREATE TABLE BINARY OF XMLType XMLTYPE STORE AS BINARY XML;

然后我通过以下方式将我的 xml 文件加载到 oracle 中:

insert into BINARY values (XMLTYPE(BFILENAME ('XML_DIR','catalog.xml'),nls_charset_id('AL32UTF8')));

到目前为止一切顺利。

现在是摘录部分:

首先我尝试了:

SELECT extract(b.object_value, '/catalog/cd/title')
FROM binary b
WHERE existsNode(b.object_value,'/catalog/cd[artist="Bob Dylan"]') = 1;

EXTRACT(B.OBJECT_VALUE,'/CATALOG/CD/TITLE')
--------------------------------------------------------------------------------

<Title>Hide your heart</Title>
<Title>Empire Burlesque</Title>

1 row selected.

这不起作用，因为 xml 文件全部在 1 行中，所以我意识到我必须将 xml 拆分为单独的行。为此，我必须使用 XMLSequence() 和 table() 函数将节点转换为虚拟表。这些函数将由 extract() 返回的两个标题节点转换为一个由两个 XMLType 对象组成的虚拟表，每个对象都包含一个标题元素。

第二次尝试:

SELECT value(d)
FROM binary b,
table (xmlsequence(extract(b.object_value,'/catalog/cd'))) d
WHERE existsNode(b.object_value,'/catalog/cd[artist="Bob Dylan"]') = 1;

VALUE(D)
--------------------------------------------------------------------------------

<cd>
    <title>Hide your heart</title>
    <artist>Bonnie Tyler</artist>
    <country>UK</country>
    <company>CBS Records</company>
    <price>9.90</price>
    <year>1988</year>
</cd>

<cd>
    <title>Empire Burlesque</title>
    <artist>Bob Dylan</artist>
    <country>USA</country>
    <company>Columbia</company>
    <price>10.90</price>
    <year>1985</year>
</cd>

2 rows selected.

这样更好，因为它现在分为 2 个不同的行，所以我应该能够选择位置并根据艺术家选择标题。

但是，这是我遇到问题的地方，我已经尝试了几个小时，但我无法弄清楚如何在我的下一个查询中使用上述查询的结果。所以我尝试通过这样做来使用 suquery:

select extract(sub1, 'cd/title')
from
(
    SELECT value(d)
    FROM binary b,
    table (xmlsequence(extract(b.object_value,'/catalog/cd'))) d
) sub1
WHERE existsNode(sub1,'/cd[artist="Bob Dylan"]') = 1;

但是sql*plus显示错误:

ORA-00904: "SUB1": invalid identifier.

我已经尝试了数十种尝试使用子查询的变体，但我似乎无法正确使用它。

我听说您也可以使用变量或 pl/sql 来做到这一点，但我不确定从哪里开始。

任何帮助将不胜感激，因为我已经尽我所能。

最佳答案

这应该有效:

SELECT EXTRACTVALUE (VALUE(xml), '*/title') title
  FROM TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE ('
        <catalog>
        <cd>
            <title>Hide your heart</title>
            <artist>Bonnie Tyler</artist>
            <country>UK</country>
            <company>CBS Records</company>
            <price>9.90</price>
            <year>1988</year>
        </cd>
        <cd>
            <title>Empire Burlesque</title>
            <artist>Bob Dylan</artist>
            <country>USA</country>
            <company>Columbia</company>
            <price>10.90</price>
            <year>1985</year>
        </cd>
        </catalog>'), '/catalog/cd'))) xml
WHERE EXTRACTVALUE (VALUE(xml), '*/artist') = 'Bob Dylan';

或者使用你的表:

SELECT EXTRACTVALUE (VALUE(xml), '*/title') title
  FROM binary b,
       TABLE (XMLSEQUENCE (EXTRACT (b.object_value, '/catalog/cd'))) xml
 WHERE EXTRACTVALUE (VALUE(xml), '*/artist') = 'Bob Dylan';

这样做是将所有 cd 节点提取到虚拟表 xml 中，为存在的每个 cd 节点返回一个结果行. WHERE 子句然后通过查看子元素 artist = 'Bob Dylan' 来限制结果，并且 SELECT 语句仅解析出内容title 元素而不是显示整个节点。

输出是:

TITLE
----------------
Empire Burlesque

关于sql - 如何从以前的xml查询结果中选择，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6529947/