XML Goodness in SQL – Part 2

In part 1, we reviewed HTTPURIType and DBURIType. Let’s now go over a few examples of more advanced XML using the SQL functions XMLTable and XMLQuery, Oracle’s native support for XQuery.

SQL functions XMLQuery and XMLTable are defined by the SQL/XML standard as a general interface between the SQL and XQuery languages. XMLQuery and XMLTable let you take advantage of the power and flexibility of both SQL and XML. Using these functions, you can construct XML data using relational data, query relational data as if it were XML, and construct relational data from XML data.

Let’s query blog titles from the OraNA.info RSS feed without using XQuery:

SQL> SELECT value(i).extract('/item/title/text()'
  2         ,'xmlns:dc="http://purl.org/dc/elements/1.1/"' ).getstringval() title
  3  FROM   TABLE (XMLSequence(
  4         HTTPURITYPE('http://feeds.feedburner.com/orana').getxml().extract('//item'))) i;

TITLE
--------------------------------------------------------------------------------
Oracle ANZ Blogosphere Highlights
Taking the Plunge: Part 2
8 Things about Louise
Working with PHP and Oracle Presentation
Removing Oracle Database XE from Linux
...

Starting with Oracle database 10gR2 you have the option to use the XQuery SQL function XMLTable. You use XMLTable to shred the result of an XQuery-expression evaluation into the relational rows and columns of a new, virtual table. You can then insert the virtual table into a pre-existing database table, or you can query it using SQL. You use XMLTable in a SQL FROM clause.

Let’s change the query above to use XMLTable and return the blog author as well:

SQL> SELECT *
  2      FROM XMLTable (
  3        '//item'
  4        passing HTTPURITYPE('http://feeds.feedburner.com/orana').getXML()
  5        COLUMNS title   varchar2(4000) path '/item/title/text()',
  6                creator varchar2(4000) path
  7                        '/item/*[namespace-uri()="http://purl.org/dc/elements/1.1/"
  8                                and local-name()="creator"]/text()'
  9     );

TITLE                                    CREATOR
---------------------------------------- --------------------
Oracle ANZ Blogosphere Highlights        Carl Terrantroy
Taking the Plunge: Part 2                Jake
8 Things about Louise                    Louise Barnfield
Working with PHP and Oracle Presentation alison.holloway
Removing Oracle Database XE from Linux   alison.holloway
...

And here is another example that selects the entries from the Oracle room on FriendFeed:

SQL> SELECT *
  2    FROM XMLTable (
  3        '/feed/entry'
  4        passing HTTPURIType ('http://friendfeed.com/api/feed/room/oraclestuff?format=xml').getXML()
  5        columns title varchar2(4000) path 'title/text()'
  6   );

TITLE
----------------------------------------
Oracle ECM and Skywire Database modeling support in SQL Developer
Oracle Fusion Middleware Strategy Webcast Replay
...

You can also use the XQuery function ora:view within an XQuery expression to query a relational table or view as if it were XML. Here is a simple example using the table we created in part 1:

SQL> SELECT *
  2    FROM XMLTable(
  3           'for $i in ora:view("t")
  4                   return $i');


COLUMN_VALUE
---------------------------------------

<ROW><A>1</A><B>Eddie</B></ROW>
<ROW><A>2</A><B>John</B></ROW>
...

The other XQuery function is XMLQuery. You use it to construct or query XML data. This function takes as arguments an XQuery expression, and an optional XQuery context item. The context item establishes the XPath context in which the XQuery expression is evaluated. Additionally, XMLQuery accepts as arguments any number of SQL expressions whose values are bound to XQuery variables during the XQuery expression evaluation. The function returns the result of evaluating the XQuery expression, as an XMLType instance.

Here are a couple of simple examples:

SQL> SELECT XMLQuery('(100 to 105)'
  2                  RETURNING CONTENT) AS output
  3    FROM DUAL;

OUTPUT
-------------------------------------------------

100 101 102 103 104 105

SQL> SELECT XMLQuery('for $i in ora:view("t")
  2                   return <ROWS>{$i}</ROWS>'
  3                  RETURNING CONTENT) AS col
  4    FROM DUAL;

COL
---------------------------------------------------
<ROWS><ROW><A>1</A><B>Eddie</B></ROW></ROWS><ROWS><

The above examples give you a “taste” of the capabilities of XQuery in Oracle. For more detailed information please refer to the following list of resources:

• Using XQuery with Oracle XML DB
• Oracle XML DB Developer’s Guide
• XQuery on WikiPedia
• What bloggers are saying about XQuery

1 Comment | Filed in Oracle | Tags: sql, xml, xquery

---

SQL>  SELECT HTTPURITYPE('http://feeds.feedburner.com/orana').getxml() orana_feed
  2     FROM dual;

ORANA_FEED
--------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet href="http://feeds.feed
...

SQL>  SELECT HTTPURITYPE('http://google.com/').getClob() goog_html
  2     FROM dual;

GOOG_HTML
--------------------------------------------------------------------------------

<html><head><meta http-equiv="content-type" content="text/html; charset=ISO-8859
...

SQL> CREATE TABLE t (a NUMBER, b VARCHAR2 (10));

Table created.

SQL> INSERT INTO  t (a,b) VALUES (1, 'Eddie');

1 row created.

SQL> INSERT INTO  t (a,b) VALUES (2, 'John');

1 row created.

SQL> INSERT INTO  t (a,b) VALUES (3, 'Pat');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT DBURIType('/SCOTT/T').getxml()
  2  from dual;

DBURITYPE('/SCOTT/T').GETXML()
---------------------------------------------------------------------------

<?xml version="1.0"?>
<T>
 <ROW>
  <A>1</A>
  <B>Eddie</B>
 </ROW>
 <ROW>
 <ROW>
  <A>2</A>
  <B>John</B>
 </ROW>
 ...

SQL> SELECT employee_id,
  2    manager_id,
  3    first_name,
  4    salary,
  5    hire_date
  6  FROM employees;

EMPLOYEE_ID MANAGER_ID FIRST_NAME               SALARY HIRE_DATE
----------- ---------- -------------------- ---------- ---------
        100            Steven                    24000 17-JUN-87
        101        100 Neena                     17000 21-SEP-89
        102        100 Lex                       17000 13-JAN-93
        103        102 Alexander                  9000 03-JAN-90
        104        103 Bruce                      6000 21-MAY-91
        105        103 David                      4800 25-JUN-97
        106        103 Valli                      4800 05-FEB-98
        107        103 Diana                      4200 07-FEB-99
        108        101 Nancy                     12000 17-AUG-94
        109        108 Daniel                     9000 16-AUG-94
        110        108 John                       8200 28-SEP-97
        ...

<cfhttp url="url_to_post_to" method="post">
   <cfhttpparam 
        type="XML" 
        name="XmlDoc" 
        value="#your_xml#">
</cfhttp>

<cfset docContent = GetHTTPRequestData().content>
<cfset myDOM = XmlParse(docContent)>