String to Rows

XML Method

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

  1. 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.
  2. See String to Columns: XML Values - 2 for more examples using XMLPARSE and EXTRACTVALUE.



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11655.html]SQL Snippets: String to Rows - XML Method[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11655.html">SQL Snippets: String to Rows - XML Method</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: String to Rows - XML Method
  • URL (href): http://www.sqlsnippets.com/en/topic-11655.html

Revision Notes

Date Category Note
2007-04-05 Revision This tutorial was moved from parent topic-11578.html to parent topic-12021.html. The ELEMENT_NAME column was added to the example. The null value gotcha example was replaced with Note 1. Note 2 was added.