CODEWITHSUNDEEP
sqlxmloracle-database
Sean B. Durkin
Sean B. Durkin

Reputation: 12729

How to get dataset from XMLType in Oracle

How does one extract a dataset from a set of xml documents stored in rows, as an SQL select statement? Here is a sample task that illustrates the question.

Input

create table circle
  ( id       number         not null primary key
  , name_xml varchar2(2000) 
  )
/

insert into circle
 select 1, '<t><person><firstn>Sean       </firstn> <lastn>Durkin     </lastn></person>' ||
              '<person><firstn>Tom        </firstn> <lastn>Sawyr      </lastn></person></t>' from dual union all
 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>' from dual union all
 select 3, null from dual;

So in table circle, we have 5 people spread across 3 table rows. Each person is identified by a first name (firstn) and a last name (lastn).

Input structure

The name_xml column is either empty or an XML documetn with root element <t>. Under <t> is any number of <person>. And under <person> is both <firstn> and <lastn> in that order. The spaces shown in the listing are just for readability and not in the real data.

Expected output

We want to get the list of full names. It should be a single string column. From the above data we expect output ...

People
----------------------- 
Sean Durkin
Tom Sawyr
Philip Marlowe
John Wayne
Constantine Palaeologus

Environment

My database engine is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi.

What I have tried so far

From what I have read and understood, this query should work ...

select extract( name_parts, '/person/firstn') || ' ' || 
       extract( name_parts, '/person/firstl') as People
 from (
 select
  extract( XMLType( name_xml), '/t/person').getStringVal() as name_parts
  from circle
  where name_xml is not null)

But this returns error inconsistent data type.

Upvotes: 1

Views: 705

Answers (1)

Kirill Leontev
Kirill Leontev

Reputation: 10931

11:28:27 SYSTEM@dwal> l
  1  select
  2   trim(extractvalue( value(t), '/person/firstn')) ||' '||
  3   trim(extractvalue( value(t), '/person/lastn')) as people
  4  from circle
  5  ,table(xmlsequence(extract(xmltype(name_xml), '/t/person'))) t
  6* where name_xml is not null
11:28:28 SYSTEM@dwal> /

PEOPLE
----------------------------------------
Sean Durkin
Tom Sawyr
Philip Marlowe
John Wayne
Constantine Palaeologus

Elapsed: 00:00:00.01

Or even simplier using XMLTable

11:36:47 SYSTEM> l
  1  select
  2    t.fname, t.lname
  3   from 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*  where name_xml is 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 for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Elapsed: 00:00:00.01

Upvotes: 3

Related Questions