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
To understand above XML code, by looking at the piece of code, we can understand, code is providing us the following data :
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.
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 )
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 :
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.
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
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"?>
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
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.
- http://oraexplorer.com/2007/10/read-xml-data-from-url-and-insert-into-oracle/
- http://www.oracle.com/technetwork/developer-tools/apex/howto-yahoo-pipe-integration-096735.html
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 :
<?xml version="1.0" encoding="ISO-8859-1"?>
<CATALOG>
<CD>
<TITLE>Empire Burlesque</TITLE>
<ARTIST>Bob Dylan</ARTIST>
<COUNTRY>USA</COUNTRY>
<COMPANY>Columbia</COMPANY>
<PRICE>10.90</PRICE>
<YEAR>1985</YEAR>
</CD>
<CD>
<TITLE>Hide your heart</TITLE>
.
.
.
</CATALOG>
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 :
SELECT EXTRACTVALUE(VALUE(ITEM), '/CD/TITLE') AS TITLE,
EXTRACTVALUE(VALUE(ITEM), '/CD/ARTIST') AS ARTIST,
EXTRACTVALUE(VALUE(ITEM), '/CD/PRICE') AS PRICE
FROM TABLE
( XMLSEQUENCE
( EXTRACT
( HTTPURITYPE
( 'http://www.w3schools.com/xml/cd_catalog.xml'
).getXML()
, '/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
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
ReplyDeleteHey friend, Thanks for your going through the blog and also thanks for your valuable feedback.
ReplyDeleteTo 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 !