sql - How to get dataset from XMLType in Oracle -

how 1 extract dataset set of xml documents stored in rows, sql select statement? here sample task illustrates question.

input

create table circle   ( id       number         not null primary key   , name_xml varchar2(2000)    ) /  insert circle  select 1, '<t><person><firstn>sean       </firstn> <lastn>durkin     </lastn></person>' ||               '<person><firstn>tom        </firstn> <lastn>sawyr      </lastn></person></t>' dual union  select 2, '<t><person><firstn>philip     </firstn> <lastn>marlowe    </lastn></person>' ||               '<person><firstn>john       </firstn> <lastn>wayne      </lastn></person>' ||               '<person><firstn>constantine</firstn> <lastn>palaeologus</lastn></person></t>' dual union  select 3, null dual; 

so in table circle, have 5 people spread across 3 table rows. each person identified first name (firstn) , last name (lastn).

input structure

the name_xml column either empty or xml documetn root element <t>. under <t> number of <person>. , under <person> both <firstn> , <lastn> in order. spaces shown in listing readability , not in real data.

expected output

we want list of full names. should single string column. above data expect output ...

people -----------------------  sean durkin tom sawyr philip marlowe john wayne constantine palaeologus 

environment

my database engine oracle database 10g enterprise edition release 10.2.0.4.0 - 64bi.

what have tried far

from have read , understood, query should work ...

select extract( name_parts, '/person/firstn') || ' ' ||         extract( name_parts, '/person/firstl') people  (  select   extract( xmltype( name_xml), '/t/person').getstringval() name_parts   circle   name_xml not null) 

but returns error inconsistent data type.

11:28:27 system@dwal> l   1  select   2   trim(extractvalue( value(t), '/person/firstn')) ||' '||   3   trim(extractvalue( value(t), '/person/lastn')) people   4  circle   5  ,table(xmlsequence(extract(xmltype(name_xml), '/t/person'))) t   6* name_xml not null 11:28:28 system@dwal> /  people ---------------------------------------- sean durkin tom sawyr philip marlowe john wayne constantine palaeologus  elapsed: 00:00:00.01 

or simplier using xmltable

11:36:47 system> l   1  select   2    t.fname, t.lname   3   circle   4   ,xmltable('/t/person'   5    passing xmltype(circle.name_xml)   6    columns   7     fname varchar2(20) path '/person/firstn',   8     lname varchar2(20) path '/person/lastn'   9  ) t  10*  name_xml not null 11:36:56 system> /  fname                lname -------------------- -------------------- sean                 durkin tom                  sawyr philip               marlowe john                 wayne constantine          palaeologus  elapsed: 00:00:00.12 11:36:58 system> @ver  banner ---------------------------------------------------------------- oracle database 10g enterprise edition release 10.2.0.3.0 - 64bi pl/sql release 10.2.0.3.0 - production core    10.2.0.3.0      production tns solaris: version 10.2.0.3.0 - production nlsrtl version 10.2.0.3.0 - production  elapsed: 00:00:00.01