logo标签列表

如何从先前的XML查询结果中进行选择

sqlxmlsubqueryoracle11gxmltype
3
我正在使用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文件只有一行，所以我意识到我必须将我的xml拆分成单独的行。为了做到这一点，我使用XMLSequence()和table()函数将节点转换为虚拟表。这些函数将extract()返回的两个title节点转换为一个虚拟表，其中包含两个XMLType对象，每个对象都包含一个单独的title元素。

第二次尝试：

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.

现在将它分成了两行，因此更好，我应该能够使用select-where来根据艺术家选择标题。

然而，这就是我遇到问题的地方，我尝试了几个小时，但无法弄清如何在下一个查询中使用上述查询的结果。因此，我尝试使用子查询来完成此操作：

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来实现这个功能，但我不确定该从何处开始。

如果有帮助的话，将不胜感激，因为我已经尽我所能尝试了所有方法。

1个回答
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节点返回一行结果。然后WHERE子句通过查看子元素artist = 'Bob Dylan'来限制结果，而SELECT语句仅解析title元素的内容而不显示整个节点。
输出是：
TITLE
----------------
Empire Burlesque
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接