How to select from a previous xml query result

I am working with an oracle 11g r2 database and basically need to be able to be able to parse and select some nodes from it. I've spent hours scouring the net and reading the oracle xml db manual trying to find an appropriate solution for my problem but I can't seem to pin down the correct way of doing this. I have a fair bit of programming experience but none with with xml, sql or oracle databases in general so pardon me if this is a trivial question.

Ok so on to the question:

I have a very simple XML file saved as catalog.xml and it is as follows:

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

Now I want to be able to extract the title of the cd given a certain artist. So for example, if the artist is 'bob dylan', then the title should be 'empire burlesque'

Now I created an XMLType table in Oracle as follows:

CREATE TABLE BINARY OF XMLType XMLTYPE STORE AS BINARY XML;

I then proceeded to load my xml file into oracle by:

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

So far so good.

Now for the extract part:

First I tried:

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.

This didn't work because the xml file was all in 1 row so I realized that I had to split my xml into seperate rows. Do do that, I had to convert the nodes into a virtual table using the XMLSequence() and table() functions. These functions convert the two title nodes retuned by extract() into a virtual table consisting of two XMLType objects, each of which contains a single title element.

Second try:

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.

This is better since it is now split into 2 different rows so I should be able to do a select-where and select the title based on the artist.

However, this is where I'm having issues, I ha开发者_如何学JAVAve tried for literally hours but I can't figure out how to use the results of the above query in my next one. So I've tried to use a suquery by doing this:

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;

However, sql*plus shows an error:

ORA-00904: "SUB1": invalid identifier.

I've tried dozens of variations of trying to use subqueries but I simly cannot seem to get it right.

I've heard that you can do also do this using variables or pl/sql but I'm not sure where to start.

Any help would be greatly appreciated as i've tried everything at my disposal.

This should work:

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';

Or using your table:

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';

What this is doing is extracting all the cd nodes into the virtual table xml, returning one result row for each cd node that exists. The WHERE clause then limits the results by looking at the child element artist = 'Bob Dylan' and the SELECT statement is parsing out only the contents of the title element rather than displaying the entire node.

The output is:

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