Currently committed XML support in Derby doesn't yet support extracting values from XML type instances. I think the only predicate allowed is XMLExists() which returns TRUE or FALSE on an XPath predicate on XML instances. You can
1) Create tables of XML datatype
2) Input XML documents using XMLParse
3) Extract XML documents using XMLSerialize
4) Query for specific XPath _expression_
There is no way to extract parts of XML documents out into SQL world, which could allow you to do order by.
However, you may be able to write your own Java function that takes serialized XML as function argument along with XPath _expression_ and return your item of interest. You could then order by on this _expression_, thanks to Tomohito's extension to ORDER BY.
If you are doing all this, then why even store XML documents in XML type and not just use CLOB or VARCHAR?
About which JARs you might need to get XML working, it depends on which JDK you are using. I think IBM versions from 1.4 or higher migh already have everything you need. SUN JDK 1.4 require xercesImpl for sure.
I think Army is working on several improvements to XML support that make JVM integration even easier (removing need for xercesImpl) and adding support for extracting XML elements, based on JIRA entries he has filed. Don't think these have been submitted yet.
Thanks for trying Derby XML... and for being brave to figure out dependencies! Hopefully 10.2 XML support would be more complete and show up in documentation.
Satheesh
On 3/31/06, Susan Cline <[EMAIL PROTECTED]> wrote:
Hi,
I've looked at some of the tests for the XML support but I am still
confused if this is possible or not. Given a table created with
an xml column:
create table xmlTab (id integer, xml_col xml);
Containing these two xml documents:
<Contact>
<id>1</id>
<firstname>Susan</firstname>
<lastname>Cline</lastname>
</Contact>
<Contact>
<id>2</id>
<firstname>Apache</firstname>
<lastname>User</lastname>
</Contact>
I'd like to come up with a query that returns results ordered by
firstname (or any other element in the XML document.)
So, in this example the document with the id of "2" would appear in
the result of the select before the document with the id of "1".
If this query is possible, can someone please post an example?
The other question I have is which other supporting jar files I need
to issue this query? I think I need the following (but can someone
confirm if specific versions are required?):
xml-apis.jar
xercesImpl.jar
xalan.jar
Thanks,
Susan
