msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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


Filed in Oracle on 08 Jul 08 | Tags: ,


Reader's Comments

  1. |

    I don’t know how I missed HTTPURIType.getxml in the Oracle docs. It makes dealing with REST web services like the Google Maps API geocoder much easier. I’d been using UTL_HTTP, getting the data in pieces as a CLOB, then casting it as an XMLTYPE. This skips all that stuff. Thanks for pointing it out.

  2. |

    @John yes, HTTPURIType is very handy. I wish it could handle basic HTTP authentication as well.