Get info from an XML column in a tabular format
I got this sample working:
with sample_data
as (select xmltype ('
<gifts>
<giftinfo gifter="SANTA CLAUS"><Recipients><kid name="JOHN GOODBOY"/><kid name="CLARK GETCOAL"/></Recipients></giftinfo>
<giftinfo gifter="TOOTH FAIRY"><Recipients><kid name="PAUL TOOTHLESS"/></Recipients></giftinfo>
<giftinfo gifter="THREE WISE MEN"><Recipients><kid name="TONNY CAMEL"/><kid name="VICTOR HORSERIDER"/><kid name="CHARLES DELEPHANT"/></Recipients></giftinfo>
<giftinfo gifter="EASTER BUNNY"><Recipients><kid name="JIMMY EGGHEAD"/></Recipients></giftinfo>
</gifts>') xmlcol
from dual)
select extractvalue (value (x), '/giftinfo/@gifter') powerhouse,
extractvalue (value (x), '/giftinfo/Recipients/kid[2]/@name') kidname,
'Holiday' environment
from sample_data t,
table (xmlsequence (extract (t.xmlcol, '/gifts/giftinfo'))) x
where extractvalue (value (x), '/giftinfo/Recipients/kid[2]/@name')
is not null
I get these results:
POWERHOUSE KIDNAME ENVIRONMENT
SANTA CLAUS CLARK GETCOAL Holiday
THREE WISE MEN VICTOR HORSERIDER Holiday
Now, I need all kid's names at once, without the need to index them with [2], is there a way to do this?
Edited by: user4531996 on May 29, 2013 1:00 PM
I tried that before, I get this error message:
ORA-19025: EXTRACTVALUE returns value of only one node