3

This is an extension to the question XPath: Select first element with a specific attribute and pertains specifically to Oracle.

Presuming we have the following data in a column called xml_data:

<bookstore>
 <category>
  <book location="CAN">A1</book>
  <book location="FIN">A2</book>
 </category>
 <category>
  <book location="FIN">B1</book>
  <book location="US">B2</book>
 </category>
 <category>
  <book location="US">C2</book>
  <book location="FIN">C1</book>
 </category>
</bookstore>

I'm trying to select the first occurrence of a book under category with attribute location="US". For the above data, that should return B2.

The suggested solution, (/bookstore/category/book[@location='US'])[1], does not work.
It returns the following error: ORA-19110: unsupported XQuery expression
Here's a SQL Fiddle demonstrating the issue.

How can I adapt the solution to work with Oracle's idea of XPaths?

Community
  • 1
  • 1
Mr. Llama
  • 20,202
  • 2
  • 62
  • 115

2 Answers2

4

It's a shame that Oracle does not support this kind of path expression. You should consider filing a bug report.

You could use a predicate that checks whether there are any preceding:: book elements (in document order) with the same attribute value. In fact, I'm almost inclined to say that this is the only XPath-only approach that works.

SELECT
    parsed.book
FROM xml_test x,
XMLTABLE(
  '/bookstore' PASSING XMLTYPE(x.xml_data)
  COLUMNS
    "BOOK" VARCHAR2(16) PATH '/bookstore/category/book[@location="US"][not(preceding::book[@location = "US"])]'
) parsed
;

which would yield the wanted result:

BOOK - B2
Mathias Müller
  • 22,203
  • 13
  • 58
  • 75
  • Ahh, I was so close and you beat me to it. Do you think preceeding:: axis would be better performance wise than the method I came up with using count? – Dan Field Apr 09 '15 at 17:32
  • @DanField Your solution only works because the first `book` element with `@location="US"` accidentally is the second child of its parent element. Try changing the input XML and see what happens. If both US books are the first child of their parents, your expression returns nothing. – Mathias Müller Apr 09 '15 at 17:35
1

Tricky way with no XQuery (just for fun):

select 
  extract(extract(xml_data,'/bookstore/category/book[@location="US"]'),'/*[1]')
from ...

Solution with XQuery works good with expression from referenced question: 

select  
  XMLQuery(
    '($xdata/bookstore/category/book[@location="US"])[1]'
    passing xml_data as "xdata"
    returning content
  )
from ...

For me original expression works well with extract on SQL Fiddle too:

SQLFiddle example

Problem with original query is that expression must be placed in XQuery itself, not in field extract path. Expression is Ok:

SELECT
    parsed.book
FROM xml_test x,
XMLTABLE(
  '(/bookstore/category/book[@location="US"])[1]' PASSING XMLTYPE(x.xml_data)
  COLUMNS
    "BOOK" VARCHAR2(16) PATH '/book'
) parsed
;

Updated:

  1. removed sentence about XPath 1.0 / 2.0 difference as source of a problem because expression works with extract() too.

  2. original solution from referenced question verified to work with extract() on SQLFiddle.

  3. Added right variant for original query

ThinkJet
  • 6,725
  • 24
  • 33
  • 1
    Interesting. There is no XPath 1.1 though, only XPath 1.0, 2.0 and 3.0. Does `XMLQuery` really mean XQuery, or does it mean evaluating a path expression? Also, I'm not sure what you mean by "Solution with XQuery works good with expression fom referenced question because" - expressions along the lines of `(...)[1]` also work in XPath 1.0. – Mathias Müller Apr 09 '15 at 18:14
  • XMLQuery is a one of [Oracle functions to work with XML](https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#ADXDB1700) and invokes XQuery on XMLType values. – ThinkJet Apr 09 '15 at 18:29
  • 1
    Odd, why does the the `(path)[1]` notation work in an `XMLQuery` but not an `XMLTable`? – Mr. Llama Apr 09 '15 at 18:46
  • Sorry for all commenters, answer updated: please review new variant. Specially thanks for @MathiasMüller for raising right questions. – ThinkJet Apr 09 '15 at 18:55