Parse XML in Oracle Using EXTRACTVALUE and XMLTABLE

                There are Multiple ways to parse XML in Oracle but in this post i will show how to parse using EXTRACTVALUE and  XMLTABLE.  Let's Suppose you have an following  XML stored  in a column MSG in table TMP.

<students>
   <student id='123'>
             <name>Ram</name>
     <marks>100</marks>
   </student>
</students>

EXTRACTVALUE 

             Now if you want to extract <name> value, you will use a function like below.

select
EXTRACTVALUE(XMLTYPE(MSG),'/students/student/name')
from TMP;

                            Above function takes MSG ( XML source ) in XMLTYPE format.( Since my MSG column is varchar i converted to XMLTYPE ) and returns the value specified in path (XPATH). I have specified absolute path here,you can also specify relative path using //name. 

                           See the following video for more detailed explanation on EXTRACVALUE and XMLTABLE 

XMLTABLE

                             EXTRACTVALUE can return only one single value. If you want to render XML as table structure like rows and columns you have to use XMLTABLE. It will create virtual table based on specification. Syntax for XMLTABLE table is as below.

select students.* from TMP,XMLTABLE(
'/students/student'
PASSING XMLTYPE(MSG)
COLUMNS
"id" NUMBER PATH '@id',
"name" VARCHAR2(20)  PATH 'name',
"marks"  NUMBER PATH 'marks'
) students;

   In above query '/students/student' is row specifier XPATH, meaning now of student tags=no of rows.  Under each Student element  id,name and marks are columns. 

                    Advantage of XMLTABLE is that it can dynamically return all the rows whereas EXTARCTVALUE can only return single row.

      

By: Ramanjaneya On 9:19 AM

Continue Reading