Sql querry for the difficult scenario

hi all,

I have one table in that only one column having data in xmlformat and remaing normal oracle datatypes.for example column name is xml_data.I need sql to get values from that xml data output data highlighted for reference in below xml data.


Sample xmldata for one of the record is like below

<?xml version="1.0" encoding="utf-8"?>

<ns0:finalvalue xmlns :ns0="http://amagdgdg.com">

<ns0:valuableitems>

<ns0:valuesfortheitems>dsddd</ns0:valuesfortheitems>

<ns0:valuesid>d21</ns0:valuesid>

<ns0:items>

<ns0:itemid>1a>/ns0:itemid>

<ns0:itemdfg>2a>/ns0:itemdfg>

<ns0:itecart>11a>/ns0:itecart>

</ns0:itesms>

<ns0:items>

<ns0:itemid>56a>/ns0:itemid>

<ns0:itemdfg>2145a>/ns0:itemdfg>

<ns0:itecart>1361a>/ns0:itecart>

</ns0:itesms>

<ns0:items>

<ns0:itemid>1a>/iwwsb0:itemid>

<ns0:itemdfg>2a>/wens0:itemdfg>

<ns0:itecart>11a>/dns0:itecart>

</ns0:itesms>

</ns0:valuableitems>

<ns0:valuableitems>

<ns0:valuesfortheitems>qqqd</ns0:valuesfortheitems>

<ns0:valuesid>dqw21</ns0:valuesid>

<ns0:items>

<ns0:itemid>1wwa>/ns0:itemid>

<ns0:itemdfg>2qa>/ns0:itemdfg>

<ns0:itecart>1ww1a>/ns0:itecart>

</ns0:itesms>

<ns0:items>

<ns0:itemid>56wwa>/ns0:itemid>

<ns0:itemdfg>21dd45a>/ns0:itemdfg>

<ns0:itecart>13a61a>/ns0:itecart>

</ns0:itesms>

<ns0:items>

<ns0:itemid>1addqa>/iwwsb0:itemid>

<ns0:itemdfg>2wa>/wens0:itemdfg>

<ns0:itecart>1aa1a>/dns0:itecart>

</ns0:itesms>

</ns0:valuableitems>

</ns0:finalvalue>

Requiredoutput:

xml_data

dsddd
qqqd

Attach (link) the xlm file as an external table
then run an import query to append the data to an internal table.