Software Engineer Thoughts

Today I was working with XMLType, and as a side effect I found interesting solution to transform list of values contained in single string to rows. You can find some solutions on Tom Kyte blog. But my solution is new approach that uses xmlType and xmlSequence

SQL> var list varchar2(200)
SQL> exec :list := '2,4,6,8,10,34,33';
PL/SQL procedure successfully completed.
SQL> select items.extract('/l/text()').getStringVal() item
 2 from table(xmlSequence(
 3 extract(XMLType('<all><l>'||
 4 replace(:list,',','</l><l>')||'</l></all>')
 5 ,'/all/l'))) items;
ITEM 
--------------------------------------------------------------------------------
2 
4 
6 
8 
10 
34 
33 
7 rows selected.

Simple! Isn’t it?
Cheers, Paweł

Unknown said...

Hey, clever indeed!
I've never put any attention to XML on Oracle, seems like I will be doing it from some time now on.

Thanks for sharing. I have included you on my blog list.

01 November, 2006 00:19

Anonymous said...

Hey Dude
your solution is Mind blowing.
I was breaking my head to solve this problem. your Idea is Awesome.
It's really cool.

17 August, 2007 15:56

Anonymous said...

Thanks, I can't say anything else.
Great!

10 December, 2007 19:32