XML DB

From: Hajaansh <hajaansh_at_hotmail.com>
Date: Thu, 16 Jan 2003 16:57:25 +0100
Message-ID: <b06km2$mcgup$1_at_ID-161794.news.dfncis.de>

Hi there!

Newbie question here ;-)

I have some XML files that i have loaded into ORACLE (in a XMLType table). Each file has got a bunch of records that i want to be able to query as if they were held in tables. Is that possible using ORACLE or should i convert the XML files into tables? i can use extractValue when there is one record per file but when there is more than one it does not work. I'll give an example of what i mean...

imagine that the files i have inserted have records that are made up records of the following stucture

<record>

<isbn>someISBN</date>
<title>aTitle</title>
<author>anAuthor</author>

	.
	.
	.etc

</record>

The table i have created is named XMLTABLE of XMLType and i have inserted a couple of these files.

Now i want to be able to do a select statement and extract all the isbn numbers and titles and authors from all the files i have inserted

SELECT extractValue(value(X),'/record/isbn') isbn
extractValue(value(X),'/record/title') title,
extractValue(value(X),'/record/author') author,

FROM XMLTABLE X works if there is one record per file but not if there is more than one. I have no idea and going round and round in circles ;-) Perhaps i am going about this completely the wrong way. Maybe i can use Oracle text in some way??

Thanks for your help.

Hajaansh Received on Thu Jan 16 2003 - 16:57:25 CET