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.
0 comments:
Post a Comment