Oracle parse xml and get tag value

When XML is stored as a string in a database column, ExtractValue with xmltype is Oracle’s built-in way to query into it. The DISTINCT is important here because XML columns often have repeated values. This approach is much cleaner than trying to parse XML with string functions.

• Create table

create table MESSAGE_ARCHIVE(
    message varchar2(200)
);

• insert xml into table

insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>1</uid></client></message>');
insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>2</uid></client></message>');
insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>3</uid></client></message>');
insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>4</uid></client></message>');
insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>3</uid></client></message>');
insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>4</uid></client></message>');

• Find all unique client uid from the xml

SELECT distinct(ExtractValue(xmltype(message),'/message/client/uid/text()'))client_uid FROM MESSAGE_ARCHIVE;

CLIENT_UID
2
1
3
4

Cleanup

• Drop table

drop table MESSAGE_ARCHIVE;