Using SQL*Plus as a Webbrowser, RSS feeder, Google Map data provider and more…

Dimitri Gielis had last Sunday a XML related question for me about the XMLDB extract function and the use of multiple namespaces. He needed the solution for the (beta) Google Map tab in DG Tournament.

You probably know the following DBURI function (available / supported since 9.0.?):

HTTPURITYPE

The following is a simple example regarding the HTML output of HTTPURITYPE (also that my US domain name registration provider is doing unasked stuff regarding c19.statcounter.com service). One of the reasons that it becomes time to switch domain name providers…


SQL> select HTTPURITYPE ('blog.gralike.com').getCLOB()
  2  from dual;

HTTPURITYPE('BLOG.GRALIKE.COM').GETCLOB()
---------------------------------------------------------------------
blog.gralike.com

Dimitri was already using this functionality for the following reasons (RSS feeder output) in his DG Tournament APEX application. Instead off HTML, a call out to receive XML data.

SQL> select * 
  2  from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

SQL> 
SQL> 
SQL> col Title for a80
SQL> 
SQL> SELECT EXTRACTVALUE (VALUE (t), 
  2                       'entry/title/text()', 'xmlns="http://www.w3.org/2005/Atom"' ) 
  3                      AS "Title"
  4  FROM TABLE (XMLSEQUENCE (EXTRACT 
  5                            (HTTPURITYPE ('http://dgielis.blogspot.com/feeds/posts/full?max-results=10').getxml ()
  6                            , '/feed/entry', 'xmlns="http://www.w3.org/2005/Atom"'))) t
  7  ;

Title
--------------------------------------------------------------------------------
Using Interactive APEX Report in a Fun way
For the European people at ODTUG
Euro 2008 betting - not possible? (DG Tournament FAQ)
APEX Meetup at ODTUG08 - Tuesday 7.15 PM
Ruby loves me too much
Using Subversion in SQL Developer... Easy?
Sorry ...
Have fun with Euro 2008 and bet
Local APEX patched successfully
APEX 3.1.1 Patch set released

10 rows selected.
.

Dimitri already had solved the Google Map implementation on his site but had some small problems on syntax issues how to address multiple namespaces in the same kind of construct by consuming the services from http://freeipservices.com/ for use in his (internal tab after login) DG Tournament Google Map.

Click on the image to enlarge

After retrieving a consumers TCP/IP number, he feeds this info into the free web service, for instance, http://api.hostip.info/?ip=141.146.8.66 (=TCP/IP number of “www.oracle.com”).

The data output will show the following (my code plugin will ruin the output a little bit)


  This is the Hostip Lookup Service
  hostip
  
    inapplicable
   
   
      Redwood City, CA
      UNITED STATES
      US
      
      
         
         
            -122.206,37.5164

The TCP/IP service can be called via a SQL*Plus session (or PL/SQL or…) via for example the following SQL statement (extract combined with multiple namespace reference and HTTPURITYPE)

SQL> select * 
  2  from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

SQL> 
SQL> col Name          for a20
SQL> col CountryName   for a20
SQL> col CountryAbbrev for a20
SQL> col Coordinates   for a20
SQL> 
SQL> 
SQL> SELECT extractvalue(value(t),'/Hostip/gml:name'
  2                          , 'xmlns:gml="http://www.opengis.net/gml" 
  3                             xmlns="http://www.hostip.info/api"')   AS "Name"
  4  ,      extractvalue(value(t),'/Hostip/countryName'
  5                          , 'xmlns:gml="http://www.opengis.net/gml" 
  6                             xmlns="http://www.hostip.info/api"')   AS "CountryName"
  7  ,      extractvalue(value(t),'/Hostip/countryAbbrev'
  8                          , 'xmlns:gml="http://www.opengis.net/gml" 
  9                             xmlns="http://www.hostip.info/api"')   AS "CountryAbbrev"
 10  ,      extractvalue(value(t),'/Hostip/ipLocation/gml:PointProperty/gml:Point/gml:coordinates'
 11                          , 'xmlns:gml="http://www.opengis.net/gml" 
 12                             xmlns="http://www.hostip.info/api"')   AS "Coordinates"
 13  FROM TABLE (XMLSEQUENCE (EXTRACT (HTTPURITYPE ('http://api.hostip.info/?ip=141.146.8.66').getxml ()
 14                           , '/HostipLookupResultSet/gml:featureMember/Hostip'
 15                           , 'xmlns:gml="http://www.opengis.net/gml"
 16                              xmlns="http://www.hostip.info/api"' ))) t
 17  /

Name                 CountryName          CountryAbbrev        Coordinates
-------------------- -------------------- -------------------- --------------------
Redwood City, CA     UNITED STATES        US                   -122.206,37.5164

1 row selected.
.

I used here the “old” table(xmlsequence(extract())) construct, which should not be used anymore if you use and Oracle 10.2 database or higher version. Instead use the XMLTABLE function for this. A post regarding table(xmlsequence(extract())) and XMLTABLE in conjunction with multiple namespace references can be found here:

The use of namespaces within XMLTable or the table(xmlsequence(extract()) constuct

With a little bit of fantasy, you should be able to implement this in your own functions.

😉

Using SQL*Plus as a Webbrowser, RSS feeder, Google Map data provider and more…

2 Comments