Hello,
I am using SQL Developer on Oracle Database 12c Enterprise Edition Release 12.1.0.2.0.
I have a table with an xmltype column whose data appears as follows:
| RECID | XMLRECORD |
| 1-1.1 | <row id='1-1.1' xml:space='preserve'><c1>Value1</c1><c1 m='2'>Value2</c1><c1 m='3'>Value3</c1><c2>ValueA</c2><c2 m='2'>ValueB</c2><c2 m='3'>ValueC</c2></row> |
I would like to extract the elements and attributes into a table as below:
| RECID | ValueNumber | ValueText |
| 1-1.1 | Value1 | ValueA |
| 1-1.1 | Value2 | ValueB |
| 1-1.1 | Value3 | ValueC |
Kindly advice how I can achieve this. I have tried using extract() together with regexp_substr to no avail.
Thank you.