- Create table
1
2
3
create table MESSAGE_ARCHIVE(
message varchar2(200)
);
- insert xml into table
1
2
3
4
5
6
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
1
2
3
4
5
6
7
SELECT distinct(ExtractValue(xmltype(message),'/message/client/uid/text()'))client_uid FROM MESSAGE_ARCHIVE;
CLIENT_UID
2
1
3
4
Cleanup
- Drop table
1
drop table MESSAGE_ARCHIVE;