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

select XMLQuery ('doc("/public/an_xml_doc.xml")/numbers/number[@id = 2]'
                 returning content) 
  from dual;

<number id="2">two</number>

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>

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:

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

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

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

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

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

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


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

October 08, 2009: On XML Queries

More on Oracle