Oracle Sql Query (XML column)

Question

I have XML column like this

<c179 m="28">Test Data</c179><c179 m="28" s="2">Test Data 2</c179> 

I want to extract data ("Test Data" and "Test Date 2") using query in oracle sql developer. (extractvalue or something)

SELECT extractValue(columnName,'/row/c179[position()=1]'),
       extractValue(columnName,'/row/c179[position()=2]')
FROM   table_Name

Currently I am trying like this and not working.

Answer 1

You can do this with just one XMLTABLE call (don't use extract or extractvalue; these have been deprecated since at least 11gR2 and they can be really slow) like so:

with your_table as (select 1 id, xmltype('<row><c179 m="28">Test Data</c179><c179 m="28" s="2">Test Data 2</c179></row>') xml_data from dual union all
                    select 2 id, xmltype('<row><c179 m="30">Row2 data</c179></row>') xml_data from dual union all
                    select 3 id, xmltype('<row><c179 m="19">Row3 data</c179><c179 m="19" s="2">Row3 data2</c179><c179 m="99" s="3">Row3 data3</c179></row>') xml_data from dual)
select yt.id,
       x.*
from   your_table yt
       cross join xmltable('/row'
                           passing yt.xml_data
                           columns c179_1 varchar2(10) path 'c179[1]',
                                   c179_2 varchar2(10) path 'c179[2]') x;

        ID C179_1     C179_2
---------- ---------- ----------
         1 Test Data  Test Data
         2 Row2 data  
         3 Row3 data  Row3 data2