Using SQL or PL/SQL to parse an XML document to extract field values -

Using SQL or PL/SQL to parse an XML document to extract field values -

i have table has next columns

col1 varchar2 col2 varchar2 col3 varchar2 col4 clob col5 varchar2 col6 varchar2 col7 varchar2 col8 varchar2

col4 clob type field contains xml document (note: not xmltype) in next format

<document> <type>documenttype</type> <authors> <author>author1</author> <author>author2</author> <author>author3</author> </authors> <documentdate>01jan2014</documentdate> <publishedcountries> <country>country1</country> <country>country2</country> </publishedcountries> </document>

i run select statement display teh values both normal columns , xml document. know how can write query can give output in 2 structuers(ie. either 1 of next structures)

structure 1

col1 col2 col3 col5 col6 col7 col8 type authors documentdate publishedcountries xx xx xx xx xx xx xx documenttype author1, author2, author3 01jan2014 country1,country2

structure 2 (i.e. lists not comma separated in individual columns)

col1 col2 col3 col5 col6 col7 col8 type author author author documentdate country country xx xx xx xx xx xx xx documenttype author1 author2 author3 01jan2014 country1 country2

i have been trying out first 1 , have far

select col1, col2, col3, col5, col6, col7, col8, extract(xmltype(col4), '//type').getstringval() type, extract(xmltype(col4), '//authors').getstringval() authors, extract(xmltype(col4), '//documentdate').getstringval() documentdate, extract(xmltype(col4), '//publishedcountries').getstringval() publishedcountries,

the above query work limited following

the lists not comma separated the tags still displayed

i think sec construction more hard dont know how insert value tag new column. preferable sql in above query if easier using pl/sql block fine too.

any ideas?

thanks in advance

edit

i struggling align column values in 2 output examples have provided. difference between 2 in first example, list items xml comma separated value in 1 single column. in sec example, list items each in own individual column.

i created table called junk mentioned columns , info , below select seems work info comma separated lists. not sure how them separate fields though.

select j.col1, j.col2, j.col3, j.col5, j.col6, j.col7, j.col8, x.* junk j, xmltable ('$d' passing xmltype(col4) "d" columns type varchar2(100) path '//type/text()', authors varchar2(100) path 'fn:string-join(//authors/author/text(), "; ")', documentdate varchar2(100) path '//documentdate/text()', publishedcountries varchar2(100) path 'fn:string-join(//publishedcountries/country/text(), "; ")') x;

hope helpfull

sql xml oracle xpath plsql