Re: XML support in 10.2 - selecting values of an XML element in an order by query?

[Satheesh Bandaram <ba...@gmail.com>]

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 <ho...@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
>

Re: XML support in 10.2 - selecting values of an XML element in an order by query?

[Susan Cline <ho...@pacbell.net>]

Hi Satheesh,

Thanks for your comments.  I've included some responses
inline below.

Susan

--- Satheesh Bandaram <ba...@gmail.com> wrote:
[ snip ]
> 
> 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.

Okay, I may or may not look into this, but thanks for the suggestion.

> 
> If you are doing all this, then why even store XML documents in XML type and
> not just use CLOB or VARCHAR?

In the application I am using I want the database to return XML.  But given
the inability of Derby to perform this type of query at the present time
I may just do the parsing of the XML document on the client.

[snip]

> 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.

You bet!
 
> On 3/31/06, Susan Cline <ho...@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.)
> >

[snip]