To take values from individual columns like these
VAL_1 VAL_2 VAL_3 NOTE
------ ------ ------ -------------------------
a One NOT NULL, Two NULL
b One NOT NULL, Two NULL
c One NOT NULL, Two NULL
a b Two NOT NULL, One NULL
a b c Three NOT NULL, Zero NULL
Zero NOT NULL, Three NULL
and create strings of XML elements from them, like this
XML_STRING -------------------------------------------------- <VAL_1>a</VAL_1> <VAL_2>b</VAL_2> <VAL_3>c</VAL_3> <VAL_1>a</VAL_1><VAL_2>b</VAL_2> <VAL_1>a</VAL_1><VAL_2>b</VAL_2><VAL_3>c</VAL_3> (null)
simply use the XMLFOREST command and the XMLTYPE method GETSTRINGVAL(), as in the following snippet.
set null "(null)"
set recsep each
select
val_1 ,
val_2 ,
val_3 ,
XMLFOREST
( val_1 as "VAL_1" ,
val_2 as "VAL_2" ,
val_3 as "VAL_3"
).GETSTRINGVAL()
as xml_string
from
t
order by
key
;
VAL_1 VAL_2 VAL_3 XML_STRING ------ ------ ------ -------------------------------------------------- a (null) (null) <VAL_1>a</VAL_1> (null) b (null) <VAL_2>b</VAL_2> (null) (null) c <VAL_3>c</VAL_3> a b (null) <VAL_1>a</VAL_1><VAL_2>b</VAL_2> a b c <VAL_1>a</VAL_1><VAL_2>b</VAL_2><VAL_3>c</VAL_3> (null) (null) (null) (null)
Note that XMLFOREST does not create elements for null values.
Elements names do not need to be distinct. If required, you can use the same element name for values from each column.
select
val_1 ,
val_2 ,
val_3 ,
xmlforest
( val_1 as "VAL" ,
val_2 as "VAL" ,
val_3 as "VAL"
).getstringval()
as xml_string
from
t
order by
key
;
VAL_1 VAL_2 VAL_3 XML_STRING ------ ------ ------ -------------------------------------------------- a (null) (null) <VAL>a</VAL> (null) b (null) <VAL>b</VAL> (null) (null) c <VAL>c</VAL> a b (null) <VAL>a</VAL><VAL>b</VAL> a b c <VAL>a</VAL><VAL>b</VAL><VAL>c</VAL> (null) (null) (null) (null)
If you want elements with a "name" attribute defined, try XMLCOLATTVAL instead of XMLFOREST.
select
val_1 ,
val_2 ,
val_3 ,
regexp_replace
( XMLCOLATTVAL
( val_1 as "VAL_1" ,
val_2 as "VAL_2" ,
val_3 as "VAL_3"
).getstringval(),
'(</column>)(<column)',
'\1'||chr(10)||'\2'
)
as xml_string
from
t
order by
key
;
VAL_1 VAL_2 VAL_3 XML_STRING
------ ------ ------ --------------------------------------------------------
a (null) (null) <column name = "VAL_1">a</column>
<column name = "VAL_2"></column>
<column name = "VAL_3"></column>
(null) b (null) <column name = "VAL_1"></column>
<column name = "VAL_2">b</column>
<column name = "VAL_3"></column>
(null) (null) c <column name = "VAL_1"></column>
<column name = "VAL_2"></column>
<column name = "VAL_3">c</column>
a b (null) <column name = "VAL_1">a</column>
<column name = "VAL_2">b</column>
<column name = "VAL_3"></column>
a b c <column name = "VAL_1">a</column>
<column name = "VAL_2">b</column>
<column name = "VAL_3">c</column>
(null) (null) (null) <column name = "VAL_1"></column>
<column name = "VAL_2"></column>
<column name = "VAL_3"></column>
Note that, unlike XMLFOREST, XMLCOLLATVAL creates empty elements for null values.
Gotchas
If you try to use single quotes instead of double quotes around the element name you will get an error.
select
xmlforest( val_1 as 'VAL_1' ) -- should be "VAL_1"
as xml_string
from
t
order by
key
;
xmlforest( val_1 as 'VAL_1' ) -- should be "VAL_1"
*
ERROR at line 2:
ORA-00931: missing identifier