Tuesday 16 April 2013

XML .. XML and XML .... How do I utilize it !

We all know that today's world demands XML data everywhere, it can be seen in our blogs as RSS feeds, can be seen in Web Services, can be heard of in SOA, also in Web 2.0. So now how do we work about it.

What can be worked upon this huge available xml data ? Lets find out through this post. We will see how to utilize the available XML data through PL/SQL.

If you want to start with knowing XML, please give a look to http://www.w3schools.com/xml/

In this post, we will be utilizing a sample XML file available at http://www.w3schools.com/xml/note.xml. Here is what it looks like

                <?xml version="1.0" encoding="ISO-8859-1"?>
                       <body>Don't forget me this weekend!</body> 

To understand above XML code, by looking at the piece of code, we can understand, code is providing us the following data :

  • message heading,
  • message body,
  • the sender, and
  • the receiver.

Lets utilize the above XML and try to fetch the information.

Before proceeding, I would like to quote the blogs which helped me to understand the usage of XML data.
Here is the SQL query which i have tried after referring the OraExplorer's post. Please have peek through of above links. I have combined the statements provided in the post and created the final SQL query to give out the data :

 select extractvalue(mydata,'/note/to') as "To",
 extractvalue(mydata,'note/from') as "From",
 extractvalue(mydata,'note/heading') as "Title",
 extractvalue(mydata,'note/body') as "Message" from (
 select sys.xmltype.createXML(sys.UriFactory.getUri('http://www.w3schools.com/'||
 'xml/note.xml').getClob()) as mydata from dual );

Screen Print for above query :

This was an easy and very interesting XML, let's try one more available at http://www.w3schools.com/xml/cd_catalog.xml. This XML gives a catalog of CD displaying the details of CD as following ( pasting only one section from XML )

                   <?xml version="1.0" encoding="ISO-8859-1"?>
                              <TITLE>Empire Burlesque</TITLE> 
                              <ARTIST>Bob Dylan</ARTIST> 
                              <TITLE>Hide your heart</TITLE> 

So this XML has a list of records unlike note.xml which had only one record. For cd_catalog.xml, i have taken help from the second post and following SQL query provide us the data :

               FROM TABLE
                    ( XMLSEQUENCE
                    ( EXTRACT
                    ( HTTPURITYPE
                        ( 'http://www.w3schools.com/xml/cd_catalog.xml'
                           , '/CATALOG/CD') ) ) ITEM
               where rownum <= 5;

 Screenshot for above query :


This was all about writing SQL query through SQL Developer or Toad. I also created an Apex page where i utilized the same XML to create an Interactive control to display a marquee section with the gathered XML data.

As the network ACL configuration @ apex.oracle.com doesn't allow us to request a URL, I am unable to place an example at apex.oracle.com.

One can retrieve the RSS Atom feed from a blog and display the blog posts in similar manner.
Eg. I can retrive RSS from KPIT's blog and display it as a content on any web page or any application as well.

Written By : Aashish Singh Negi ( http://apex.aashishnegi.com )
Contributor : Vijeta Digarse


  1. Ashish you have a good article there. Even I'm a APEX developer but lately thinking if its any good investing time in this technology. I couldnt find any interesting job openings in India for this tech. Let me know your thoughts and if we connect some time on google hangouts or something

  2. Hey friend, Thanks for your going through the blog and also thanks for your valuable feedback.

    To answer your question, I feel it is a surely a great time to invest in this technology and comparing any other RAD tool, I find it quite beneficial over others.

    I agree with the later part of your comment about the job opening for Apex in India.

    You can surely connect to me on Google Plus. Please use my other mail id negiaashish20063120@gmail.com for same.

    Have a nice day ahead !