db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Satheesh Bandaram" <banda...@gmail.com>
Subject Re: XML support in 10.2 - selecting values of an XML element in an order by query?
Date Sat, 01 Apr 2006 18:57:47 GMT
Hi Susan,

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 <home4slc@pacbell.net> 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
>

Mime
View raw message