XML Goodness in SQL - Part 1

Here are some cool and useful things you can do with XML using pure SQL in an Oracle database.

HTTPURIType

The HTTPURIType provides support for the HTTP protocol. It uses the UTL_HTTP package underneath to access the HTTP URLs. With HTTPURIType, you can create objects that represent links to remote Web pages (or files) and retrieve those Web pages by calling HTTPURIType member methods.

The getXML() member function returns the XMLType located at the address specified by the URL. An error is thrown if the address does not point to a valid XML document.

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

The getClob() member function returns the CLOB located by the HTTP URL address.

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

DBURIType

The DBURIType provides support for DBUri-refs. A DBUri-ref is an intra-database URL that can be used to reference any row or row-column data in the database. With DBURIType, you can create objects that represent links to database data, and retrieve such data as XML by calling DBURIType member methods.

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.

The member function getXML() returns the XMLType located at the address specified by the URL.

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

The above was tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.

In part 2 we’ll explore more XML goodness…

Sources and resources

Possibly related:

Tagged , | Post a Comment | Trackback URI