By Yuli Vasiliev
Can you really access Web data from your database? If you’re an Oracle database user, it is much easier than you think. The ability to access data stored outside of the database is one of those things that make Oracle Database unequivocally best in class. No matter where the data you need is located. No matter in what format it is stored. If it can be reached via standard protocols such as HTTP, you will be able to access it from within your database. This article reveals how you might access Web data with SQL, extracting necessary information from it and converting the results into relational format if necessary.
In the following example, you use SQL/XML function XMLQuery to build an XML document with the data derived from an external RSS feed. In particular, it queries Oracle RSS feed OTN – Headlines, extracting only those items that include substring Oracle SOA in the title.
The output might look like this:
To take things even further, you may want to have the results returned in relational format, fully extracted from XML tags. This is where SQL/XML function XMLTable comes in handy. In the following example, you query the same RSS feed, retrieving the results in relational format:
This is what the output should look like:
Once you have the data you need shredded into relational rows in a virtual table, you can join it with the data in another table, either regular or virtual.
It is interesting to note that you may access not only static HTTP sources as it was illustrated in the above example, but also HTTP-callable APIs generating the result dynamically.
Below is a simple example of using HTTPURITYPE to access a HTTP callable API that returns your IP address in plain text format:
About the Author
Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, business intelligence (BI), databases, and service-oriented architecture (SOA). He is the author of Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting (Packt, 2010) and PHP Oracle Web Development (Packt, 2007) as well as a series of other books on the Oracle technology.
