如何从之前的xml查询结果中进行选择

Question

我正在使用 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 的标题。例如，如果艺术家是“bob dylan”，那么标题应该是“empire burlesque”

现在我在 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 来做到这一点，但我不知道从哪里开始。

任何帮助将不胜感激，因为我已经尝试了我可以使用的一切。

Answer 1

这应该有效：

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< 返回一个结果行/code> 存在的节点。然后，WHERE 子句通过查看子元素 artist = 'Bob Dylan' 来限制结果，并且 SELECT 语句仅解析内容title 元素的而不是显示整个节点。

输出是：

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