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.

Anton Zaviriukhin · Accepted Answer · 2016-03-21 17:49:41Z

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

Marmite Bomber · Accepted Answer · 2016-03-21 18:21:20Z

3

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

answered Mar 21, 2016 at 18:21

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

3 Comments

Burhan Khalid Butt Over a year ago

Thank you all for your valuable answers.

Burhan Khalid Butt Over a year ago

your answer was the exact solution I was looking for. Thank you so much. :)

Marmite Bomber Over a year ago

For the XML part all credits should go to @Anton - I added only the pivot part:)

Collectives™ on Stack Overflow

XML data extraction in oracle

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related