This tutorial shows how to extract values from strings of XML elements like this one
DATA_SET STRING ------------------------- ---------------------------------------- XML - 2 <V1>a</V1> <V2>b,b</V2> <V3>c c c</V3>
and return the values in separate columns like this.
DATA_SET VAL_1 VAL_2 VAL_3 ------------------------- ------ ------ ------ XML - 2 a b,b c c c
Warning: The snippets in this tutorial do not work as expected when the source data contains NULL values (as tested in Oracle 10g XE, see the Gotchas section of the XML Values - 1 tutorial). NULL values have therefore been excluded from the test data.
In the
XML Values - 1
tutorial we saw solutions for extracting values from XML fragments when each element in the fragment had
the same name.
Below we will see how slight modifications of those solutions are used to extract values from
XML fragments when the element names differ from each other.
In each case the only change needed is to replace XPath expressions like
/V[1]/text()
with expressions like
/V1/text()
Extracting Values with the EXTRACT and GETSTRINGVAL Member Functions
SQL Solution
select
t1_xml_2a.num ,
t1_xml_2a.xml_instance ,
t1_xml_2a.xml_instance.extract('/V1/text()').getstringval() as val_1 ,
t1_xml_2a.xml_instance.extract('/V2/text()').getstringval() as val_2 ,
t1_xml_2a.xml_instance.extract('/V3/text()').getstringval() as val_3
from
( select num, xmlparse( content string wellformed ) as xml_instance
from t1_xml_2
) t1_xml_2a
order by num ;
NUM XML_INSTANCE VAL_1 VAL_2 VAL_3 ---- ---------------------------------------- ------ ------ ------ 1 <V1>a</V1> a 2 <V1>a</V1> <V2></V2> a 3 <V1>a</V1> <V2></V2> <V3></V3> a 4 <V1></V1> <V2>b,b</V2> <V3></V3> b,b 5 <V1></V1> <V2></V2> <V3>c c c</V3> c c c 6 <V1>a</V1> <V2>b,b</V2> a b,b 7 <V1>a</V1> <V2>b,b</V2> <V3></V3> a b,b 8 <V1>a</V1> <V2>b,b</V2> <V3>c c c</V3> a b,b c c c 9 <V1></V1> 10 <V1></V1> <V2></V2> 11 <V1></V1> <V2></V2> <V3></V3>
PL/SQL Solutions
begin
dbms_output.put_line( '
num VAL_1 VAL_2 VAL_3
---------- ---------- ---------- ----------
' );
for t1_xml_2_row in
( select
t1_xml_2a.num ,
t1_xml_2a.xml_instance ,
t1_xml_2a.xml_instance.EXTRACT('/V1/text()').GETSTRINGVAL() as val_1 ,
t1_xml_2a.xml_instance.EXTRACT('/V2/text()').GETSTRINGVAL() as val_2 ,
t1_xml_2a.xml_instance.EXTRACT('/V3/text()').GETSTRINGVAL() as val_3
from
( select t1_xml_2.num, xmlparse(content t1_xml_2.string wellformed) as xml_instance
from t1_xml_2
) t1_xml_2a
order by num
) loop
dbms_output.put_line
( rpad( t1_xml_2_row.num, 11 ) ||
rpad( nvl( t1_xml_2_row.val_1, ' ' ), 11 ) ||
rpad( nvl( t1_xml_2_row.val_2, ' ' ), 11 ) ||
rpad( nvl( t1_xml_2_row.val_3, ' ' ), 11 )
);
end loop;
end;
/
num VAL_1 VAL_2 VAL_3
---------- ---------- ---------- ----------
1 a
2 a
3 a
4 b,b
5 c c c
6 a b,b
7 a b,b
8 a b,b c c c
9
10
11
begin
dbms_output.put_line( '
num VAL_1 VAL_2 VAL_3
---------- ---------- ---------- ----------
' );
for t1_xml_2_row in
( select num, xmlparse( content string wellformed ) as xml_instance
from t1_xml_2
order by num
) loop
declare
x_val_1 xmltype := t1_xml_2_row.xml_instance.extract('/V1/text()') ;
x_val_2 xmltype := t1_xml_2_row.xml_instance.extract('/V2/text()') ;
x_val_3 xmltype := t1_xml_2_row.xml_instance.extract('/V3/text()') ;
val_1 varchar2(10) :=
CASE when x_val_1 is null then null else x_val_1.getStringVal() end ;
val_2 varchar2(10) :=
CASE when x_val_2 is null then null else x_val_2.getStringVal() end ;
val_3 varchar2(10) :=
CASE when x_val_3 is null then null else x_val_3.getStringVal() end ;
begin
dbms_output.put_line
( rpad( t1_xml_2_row.num, 11 ) ||
rpad( nvl( val_1, ' ' ), 11 ) ||
rpad( nvl( val_2, ' ' ), 11 ) ||
rpad( nvl( val_3, ' ' ), 11 )
);
end;
end loop;
end;
/
num VAL_1 VAL_2 VAL_3
---------- ---------- ---------- ----------
1 a
2 a
3 a
4 b,b
5 c c c
6 a b,b
7 a b,b
8 a b,b c c c
9
10
11
Extracting Numbers with the EXTRACT and GETNUMBERVAL Member Functions
column n_val_1 format 999999
column n_val_2 format 999999
column n_val_3 format 999999
select
t1_xml_3.num ,
t1_xml_3.xml_instance ,
t1_xml_3.xml_instance.extract('/V1/text()').GETNUMBERVAL() as N_VAL_1 ,
t1_xml_3.xml_instance.extract('/V2/text()').GETNUMBERVAL() as N_VAL_2 ,
t1_xml_3.xml_instance.extract('/V3/text()').GETNUMBERVAL() as N_VAL_3
from
( select
1 as num,
xmlparse(content '<V1>5</V1> <V2></V2> <V3>46</V3>' wellformed) as xml_instance
from dual
) t1_xml_3
;
NUM XML_INSTANCE N_VAL_1 N_VAL_2 N_VAL_3 ---- ---------------------------------------- ------- ------- ------- 1 <V1>5</V1> <V2></V2> <V3>46</V3> 5 46
Extracting Values With the SQL EXTRACTVALUE Function
select
t1_xml_2a.num ,
t1_xml_2a.xml_instance ,
EXTRACTVALUE( t1_xml_2a.xml_instance, '/V1/text()' ) as VAL_1 ,
EXTRACTVALUE( t1_xml_2a.xml_instance, '/V2/text()' ) as VAL_2 ,
EXTRACTVALUE( t1_xml_2a.xml_instance, '/V3/text()' ) as VAL_3
from
( select num, xmlparse( content string wellformed ) as xml_instance
from t1_xml_2
) t1_xml_2a
order by num ;
NUM XML_INSTANCE VAL_1 VAL_2 VAL_3 ---- ---------------------------------------- ------ ------ ------ 1 <V1>a</V1> a 2 <V1>a</V1> <V2></V2> a 3 <V1>a</V1> <V2></V2> <V3></V3> a 4 <V1></V1> <V2>b,b</V2> <V3></V3> b,b 5 <V1></V1> <V2></V2> <V3>c c c</V3> c c c 6 <V1>a</V1> <V2>b,b</V2> a b,b 7 <V1>a</V1> <V2>b,b</V2> <V3></V3> a b,b 8 <V1>a</V1> <V2>b,b</V2> <V3>c c c</V3> a b,b c c c 9 <V1></V1> 10 <V1></V1> <V2></V2> 11 <V1></V1> <V2></V2> <V3></V3>