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() --------------------------------------------------------------------- <html><head><title>blog.gralike.com</title> <meta NAME="keywords" CONTENT="gralike oracle ORACLE xml db XMLDB xpath storage vmware xquery"> </meta><meta NAME="description" CONTENT="blog.gr alike.com: about Oracle, XMLDB and other interests"> </meta><meta NAME="ROBOTS" CONTENT="ALL, INDEX, FOLLOW "> </meta><meta HTTP-EQUIV="Content-Type" CONTENT="text/html; chars et=iso-8859-1"> </meta><meta HTTP-EQUIV="Content-Language" CONTENT= "en-us"> </meta><meta NAME="language" CONTENT="English"> <script TYPE="text/javascript" LANGUAGE="javascri pt"> var sc_project=2031732; var sc_invisible=1; var sc_partition=18;</script></meta></head></html> |
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)
<hostiplookupresultset version="1.0.0" xsi:schemaLocation="http://www.hostip.info/api/hostip-1.0.0.xsd"> <gml :description>This is the Hostip Lookup Service</gml> <gml :name>hostip</gml> <gml :boundedBy> </gml><gml :Null>inapplicable</gml> <gml :featureMember> <hostip> <gml :name>Redwood City, CA</gml> <countryname>UNITED STATES</countryname> <countryabbrev>US</countryabbrev> <!-- Co-ordinates are available as lng,lat --> <iplocation> <gml :PointProperty> </gml><gml :Point srsName="http://www.opengis.net/gml/srs/epsg.xml#4326"> </gml><gml :coordinates>-122.206,37.5164</gml> </iplocation></hostip></gml> </hostiplookupresultset> |
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:
With a little bit of fantasy, you should be able to implement this in your own functions.
2 comments
Grimm
9 June, 2008 at 15:04 (UTC 1) Link to this comment
Very nice Marco!
Anonymous
9 June, 2008 at 15:04 (UTC 1) Link to this comment
Great solution Marco! Love it! 101%!