Skip to Main Content

DevOps, CI/CD and Automation

Get info from an XML column in a tabular format

user4531996May 29 2013 — edited May 31 2013
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
This post has been answered by Jason_(A_Non) on May 29 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 28 2013
Added on May 29 2013
11 comments
5,173 views