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.

Comments

Leave a comment! An empty line creates a new paragraph. Urls are recognized if they either start with www. or with http://. In order to post code in non-proportional font, enclose the code in [code] and [/code]. Be sure that [code] and [/code] are both on a single line with nothing else. See also this link for an example.

Name: