Search in a xml file which is stored in a text column

[asd 123]

Hello,

I have few xml files (size varies from 100 - 200kb ) of same format. Each of them are stores in a text cloumn (a kiund of blob) inside a table.


Is there any way that I can make a query, which searches in all xml files at a time return the values from xml.

for e.g.

XML file looks some thing like

<?xml version="1.0" encoding="ISO-8859-1"?>

<bookstore>

<book category="COOKING">
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>

<book category="CHILDREN">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>

<book category="WEB">
<title lang="en">XQuery Kick Start</title>
<author>James McGovern</author>
<author>Per Bothner</author>
<author>Kurt Cagle</author>
<author>James Linn</author>
<author>Vaidyanathan Nagarajan</author>
<year>2003</year>
<price>49.99</price>
</book>

<book category="WEB">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>

</bookstore>





I want to retrive the name of the author whose price is more than 34.


Then the query should return James McGovern,Erik T. Ray.

This is just one xml file stored in a text cell inside a table. I want the query tosearch all the rows and give me the result


At the moment the table looks like


Primary key (int) ----------file_name (varchar) -----------------filedata(LONG TEXT)
1 ---------------------output1 ---------------------TEXT(file)
2 ---------------------output2 ---------------------TEXT(file)
3 ---------------------output3 ---------------------TEXT(file)




thanks,



Edited 1 time(s). Last edit at 04/25/2012 04:00AM by asd 123.