René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

October 08, 2009: On XML Queries

I've been playing with Oracle's implementation for XQueries recently. Here are a few queries to demonstrate some features of XQueries. By no means are they complete, but they're meant to give a quick overwiev on some possibilities.
First, I create an XDB resource for the XML document (named an_xml_doc.xml) on which I want to execute the XQueries:
set serveroutput on size 10000

declare
  success boolean;
begin

  success := dbms_xdb.createResource(
    -- Name of resource:
    '/public/an_xml_doc.xml',
    -- Document content:
    '<numbers>
      <number id= "1">one</number>
      <number id= "2">two</number>
      <number id="10">ten</number>
      <number id= "6">six</number>
    </numbers>');

  if success then
     dbms_output.put_line('success');
  else
     dbms_output.put_line('no success');
  end if;

end;
/
success
Extract all <number> nodes under a <numbers> node whose id is equal to 2.
select XMLQuery ('doc("/public/an_xml_doc.xml")/numbers/number[@id = 2]'
                 returning content) 
  from dual;
<number id="2">two</number>
Same thing, but use a variable and explicitely return the variable's value:
select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number[@id = 2]
                  return $num'
                  returning content) 
  from dual;
<number id="2">two</number>
Return all <number> nodes under a <numbers> node without the id restriction:
select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number
                  return $num'
                  returning content) 
  from dual;
<number id="1">one</number><number id="2">two</number><number id="10">ten</numbe
It turns out that SQL*Plus cuts the returned string at position 80. This is the default maximum displayed length for longs. So, we have to increase the maximum length:
set long 10000
Same query...
select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number
                  return $num'
                  returning content) 
  from dual;
... but now with entire result:
<number id="1">one</number><number id="2">two</number><number id="10">ten</numbe
r><number id="6">six</number>
I am only interested in the nodes' content (that is, without the <number>...</number>). The text() operator comes in handy:
select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number
                  return $num/text()'
                  returning content) 
  from dual;
onetwotensix
The text() operator can also be placed elsewhere:
select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number/text()
                  return $num'
                  returning content) 
  from dual;
onetwotensix
Unfortunately, the boundaries between the words are not clear. So, I use the for .. in construct and append a whitespace to each word:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number/text()
                  return ($num, " ")'
                  returning content) 
  from dual;
one  two  ten  six
Ordering the returned value:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number order by $num/text()
                  return ($num/text()," ")'
                  returning content) 
  from dual;
one  six  ten  two
Still ordering the returned value, but returning the value of the attribute id instead of the node's content:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number order by $num/text()
                  return ($num/text()/@id," ")'
                  returning content) 
  from dual;
1  6  10  2
I want the result ordered by the id, not by the text value:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number order by $num/@id
                  return ($num/text()," ")'
                  returning content) 
  from dual;
one  ten  two  six
This is, however, not exactly what I wanted. Although the query ordered the result by the id, it used the id's ascii value, not the numerical value (that is "1" < "10" < "2" < "6").
So, I am going to force using the numerical value by applying the number operator:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number order by number($num/@id)
                  return ($num/text()," ")'
                  returning content) 
  from dual;
one  two  six  ten
Finally, I only want values whose id is greater than 5:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number 
                  where number($num/@id) > 5
                  order by number($num/@id)
                  return ($num/text()," ")'
                  returning content) 
  from dual;
six  ten

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.

Warning: require(/var/www/virtual/adp-gmbh.ch/forum/comment.inc): failed to open stream: No such file or directory in /home/adpgmbh/public_html/blog/2009/10/08.php on line 457

Fatal error: require(): Failed opening required '/var/www/virtual/adp-gmbh.ch/forum/comment.inc' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/adpgmbh/public_html/blog/2009/10/08.php on line 457