Is it possible to sort XML indexed data.

[grjohnson]

I have a table

Employee

id varchar2(50)
xml_data clob

Is is possible to order data stored in a query.

id, xml_data
1, lessthansign employee greaterthansign lessthansign name 'fred' greaterthansign/name greaterthansign lessthansign agegreaterthansign'26'lessthansign/age greaterthansign''lessthansign/employee greaterthansign'

(Sorry, but when I put all the signs in myself, the forum think I'm coding XML).

i.e if I wanted to order the records returned in a query by the tag. I have been informed that oracle CANNOT do this. Do you know of any products or tools that are able to link with Oracle and manipulate and order the results or XML data stored in an CLOB datastore and intermedia indexed?

Thanks,



[Edited by grjohnson on 07-23-2001 at 01:40 AM]

[cglover]

Answer is "no". But there are work-arounds. The best way to achieve your goal here is to "factor" some data out of the XLM column into other columns. Going with your example, you might want to add a column to your table called emp_name. You could then index this colum to provide you with the ability to order your XML by emp_name. This is the smartest way to do this.

Another way to sort your XML, the very inefficient and slow way, would be to build a function that returns the value of an element in your XML. You could then order your query results by the returned value from your function. Might look like this:

SELECT xml_data
FROM xml_table
ORDER BY getElementData(xml_data, 'NAME');

Hope this helps.

-Christopher