Database Software

Hey all,

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 <title> 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 have 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.