XML data extraction in oracle

Question

I have an XML as follows:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<EXPORT>
    <DATA>
        <Row>
            <Col>0</Col>
            <Col>COMP-00001</Col>
            <Col>rg</Col>
            <Col>Preliminary</Col>
            <Col/>
            <Col>COMP - Crystal</Col>
        </Row>
        <Row>
            <Col>0</Col>
            <Col>COMP-00004</Col>
            <Col>Test1</Col>
            <Col>Preliminary</Col>
            <Col>A</Col>
            <Col>COMP - Crystal</Col>
        </Row>
        <Row>
            <Col>0</Col>
            <Col>COMP-00016</Col>
            <Col>Test123</Col>
            <Col>Released</Col>
            <Col>A</Col>
            <Col>COMP - Crystal</Col>
        </Row>
    </DATA>
</EXPORT>

How can I extract data from such format of XML. I have been trying the following code, but it does not seem to work

SELECT vt.COL_v1
  FROM   XML_REPOSITORY_TAB fm
  ,      XMLTABLE('*'
                  PASSING fm.XML_DATA
                   COLUMNS
                     xmlresult      XMLTYPE PATH '/EXPORT/DATA'
                 ) xt
  ,      XMLTABLE('*'
                   PASSING xt.xmlresult
                   COLUMNS
                     COL_v1 VARCHAR2(100) PATH 'Col/text()'
                 ) vt
                 where fm.id= 1

Can anybody tell me, what and where I am doing it wrong.

Answer 1

If you have constant number of Col elements in each Rowyou may aditionally add `PIVOT' to teh solution proposed by @Anton and get a relational view of the data.

with col as (
SELECT  
  x.row_no,
  col_no,
  value
FROM XML_REPOSITORY_TAB t,
  XMLTable( 'for $i in /EXPORT/DATA/Row     
            return $i' passing (t.xml_data) columns 
  row_no FOR ORDINALITY,
  cols  XMLType path '*'  ) x,
  XMLTable( 'for $i in /Col     
            return $i' passing (x.cols) columns 
  col_no FOR ORDINALITY,
  value   varchar2(100) path '/Col'  ) y
)
select * 
from col  
PIVOT (max(value)    for (col_no) in 
(1 as "COL1",
'2' as "COL2",
'3' as "COL3",
'4' as "COL4",
'5' as "COL5",
'6' as "COL6")
)
;

.

    ROW_NO COL1    COL2        COL3     COL4         COL5     COL6  
---------- ------  --------    -------  --------     -------- --------
         1 0       COMP-00001  rg       Preliminary           COMP - Crystal
         2 0       COMP-00004  Test1    Preliminary  A        COMP - Crystal      
         3 0       COMP-00016  Test123  Released     A        COMP - Crystal

Answer 2

If you need just list of values stored in Col tags, you may modify your query as follows:

SELECT xt.COL_v1
  FROM   XML_REPOSITORY_TAB fm
  ,      XMLTABLE('/EXPORT/DATA/Row/Col'
                  PASSING fm.XML_DATA
                   COLUMNS
                     COL_v1     XMLTYPE PATH 'text()'
                 ) xt
                 where fm.id= 1

If you also needs number of Row tag and Col tag from your XML, you may improve this query:

SELECT row_num, col_num, COL_
  FROM   XML_REPOSITORY_TAB fm
  ,      XMLTABLE('/EXPORT/DATA/Row'
                  PASSING fm.XML_DATA
                   COLUMNS
                     row_num        FOR ORDINALITY,
                     row_           XMLTYPE PATH '*'
                 ) xrow
  ,      XMLTABLE('/Col'
                   PASSING xrow.row_
                   COLUMNS
                     col_num        FOR ORDINALITY,
                     COL_           VARCHAR2(100) PATH 'text()'
                 ) xcol
                 where fm.id= 1