You cab use XMLTABLE to fetrch values from the XML. If the data is in varchar, change it first to XMLTYPE in the query.
Consider the following example
Create the test table
CREATE TABLE XmlTestData (
TheData varchar2(2000)
);
Add the test row
INSERT INTO XmlTestData (TheData)
VALUES ('<PivotSet>
<item>
<column name = "RNO">1</column>
<column name = "MIN(INSGRP)">Record- 001</column>
</item>
<item>
<column name = "RNO">2</column>
<column name = "MIN(INSGRP)">Record- 002</column>
</item>
<item>
<column name = "RNO">3</column>
<column name = "MIN(INSGRP)">Record- 003</column>
</item>
<item>
<column name = "RNO">4</column>
<column name = "MIN(INSGRP)">Record- 004</column>
</item>
<item>
<column name = "RNO">5</column>
<column name = "MIN(INSGRP)">Record- 005</column>
</item>
<item>
<column name = "RNO">6</column>
<column name = "MIN(INSGRP)">Record- 006</column>
</item>
</PivotSet>');
COMMIT;
Run the query which uses the following logic:
- select the data from the table
- extra each item as separate row
- extract the columns from a single item
SELECT TO_NUMBER(extractvalue(res.RNO, 'text()')) AS RNO,
TO_CHAR(extractvalue(res.MIN_INSGRP, 'text()')) AS MIN_INSGRP
FROM XmlTestData xtd,
XMLTABLE('/PivotSet/item'
PASSING XMLTYPE(xtd.TheData)
COLUMNS XmlItem XMLTYPE PATH 'column'
) items,
XMLTABLE('/'
PASSING items.XmlItem
COLUMNS
RNO XMLTYPE PATH 'column[@name="RNO"]/text()',
MIN_INSGRP XMLTYPE PATH 'column[@name="MIN(INSGRP)"]/text()'
) res;
The result is
RNO MIN_INSGRP
--- ----------
1 Record- 001
2 Record- 002
3 Record- 003
4 Record- 004
5 Record- 005
6 Record- 006