This tutorial shows how to extract delimited values from XML fragments like these
C ----------------------------------- <V1>a</V1> <V2>b</V2> <V3>c</V3>
and return a table containing one value per row, like this.
ELEMENT_NAME VAL ------------ ---------- V1 a V2 b V3 c
Before proceeding though, let's look at our test data.
set null "(null)" select * from t5 ;
KEY C NOTE ---------- ----------------------------------- ------------------------- r01 <V1>a</V1> One value r02 <V1>a</V1> <V2></V2> Two values, one empty r03 <V1>a</V1> <V2></V2> <V3></V3> Three values, two empty r04 <V1></V1> <V2>b</V2> <V3></V3> Three values, two empty r05 <V1></V1> <V2></V2> <V3>c</V3> Three values, two empty r06 <V1>a</V1> <V2>b</V2> Two values r07 <V1>a</V1> <V2>b</V2> <V3></V3> Three values, one empty r08 <V1>a</V1> <V2>b</V2> <V3>c</V3> Three values, none empty r09 <V1></V1> One empty value r10 <V1></V1> <V2></V2> Two values, two empty r11 <V1></V1> <V2></V2> <V3></V3> Three values, all empty r12 (null) NULL string
The extraction can be accomplished in one SELECT statement using XMLPARSE , XMLSEQUENCE , and EXTRACTVALUE as follows.
set pagesize 45 break on key skip 1 duplicates column element_name format a12 select t5.key , t5a.column_value.getRootElement() as element_name , EXTRACTVALUE( t5a.column_value , '/*/text()' ) val from t5 , table ( XMLSEQUENCE ( XMLPARSE(content t5.c wellformed ) ) ) t5a where t5.c is not null -- see Note 1 ;
KEY ELEMENT_NAME VAL ---------- ------------ ---------- r01 V1 a r02 V1 a r02 V2 (null) r03 V1 a r03 V2 (null) r03 V3 (null) r04 V1 (null) r04 V2 b r04 V3 (null) r05 V1 (null) r05 V2 (null) r05 V3 c r06 V1 a r06 V2 b r07 V1 a r07 V2 b r07 V3 (null) r08 V1 a r08 V2 b r08 V3 c r09 V1 (null) r10 V1 (null) r10 V2 (null) r11 V1 (null) r11 V2 (null) r11 V3 (null)
Notes
- See the "Gotchas: NULL Values" section under String to Columns: XML Values - 1 to see why we deliberately exclude row 12, the one with a NULL string, from the results above.
- See String to Columns: XML Values - 2 for more examples using XMLPARSE and EXTRACTVALUE.