Hi i am using 11g Release2, 64 bit oracle database.
Question is
1)Below is the code i am using,
WITH T AS (SELECT XMLTYPE('<ALL_REGIONS>
<COUNTRY_CODE>
<COUNTRY ID="001"/>
<COUNTRY ID="002"/>
<COUNTRY ID="003"/>
<COUNTRY ID="004"/>
<COUNTRY ID="005"/>
</COUNTRY_CODE>
</ALL_REGIONS>') XMLCOL
FROM DUAL)
SELECT EXTRACT(VALUE(X),'/ALL_REGIONS/COUNTRY_CODE/COUNTRY/@ID') as "id" from t,TABLE(XMLSequence(extract(t.xmlcol,'/'))) x;
2)Output is
id
-------------------
001002003004005
1 row selected.
3)I need output to be like
id
--------------------
001
002
003
004
005
Can Anyone tell me how to do so, it's urgent ..... please mail to lokesh3012@live.com