How to Parse Simple XML tags Data in ORACLE Query.

By: Arif Khan.

Hi there. Most people think that parsing XML Tag data in oracle is a difficult job. Believe me it is as simple as

(Select * From Employee).

Let me explain you step by step.

What is XML (Extensible Markup Language) and its uses?

XML means Extensible Markup Language. Actually it is a text-based markup language derived from (SGML) Standard Generalized Markup Language. XML tags identify the data and are used to organize and store the data, it does not specify data display sequence like HTML. XML is extensively used in today’s web world like banking services, online stores, integrating systems etc. One can put as many different types of information in the XML as needed and the document still remains simple.

Simple XML example document.

You can understand the data, by just looking at the below example XML document. I used self-describing tags in this example XML document.

<?xml version="1.0" encoding="UTF-8"?>
<student>
<rollno> 215 </rollno>
<name> Abdul Baseer Khan </name>
<fname> Abdul Raheem Khan </fname>
<age> 22 </age>
<trade> Pre-Medical </trade>
</student>

Document Understanding:

First line of document <?xml version=”1.0″ encoding=”UTF-8″?> is known as XML Prolog. It is optional, if we use this line, it should always be the first line of XML document. It defines the XML version and the encoding used in XML document.

The tag <student > is the root of this document. A XML document always starts with root element. So, every XML document should have a root element and at the end of the document this root element needs to be closed by closing tag, just like </student > as you see in above example document. In between root element every element should start and end like <rollno></rollno> and so on.

Parsing the document:

Now let’s try to parse above example XML using Oracle Query block.

The Oracle SQL Query:

SELECT rollno, NAME, fname, age, trade

FROM XMLTABLE(‘/student’ PASSING xmltype(‘

1.SELECT rollno, NAME, fname, age, trade
2.FROM XMLTABLE('/student' PASSING xmltype('

<student>
 <rollno>215</rollno>
 <name>Abdul Baseer Khan</name>
 <fname>Abdul Raheem Khan</fname>
 <age>22</age>
 <trade>Pre-Medical</trade>
</student>') 

3.COLUMNS 
4.rollno PATH 'rollno',
5.name PATH 'name',
6.fname PATH 'fname',
7.age PATH 'age',
8.trade PATH 'trade');

In line 2 I just put the example XML document with XPATH starting point like ‘/student’ in front of FROM clause as XMLTABLE within single quotes, at line 3 I used COLUMNS clause, it is used to define the tag names and their XPATH from there Oracle Query will extract the XML Tag data. While the line number 1 is simply selecting extracted columns from XML Tags. After execution above Oracle Query will give you below.

Oracle Query Result:

      ROLLNO   NAME              FNAME             AGE   TRADE

215         Abdul Baseer Khan Abdul Raheem Khan 22    Pre-Medical

I hope that you will understand the XML basics and its parsing process in Oracle Query.

Hearing from you, will be my pleasure, If you have any comments or questions, please post below.

Enjoy and happy Coding.