CODEWITHSUNDEEP
xmloracle-databaseclob
Akshay Kapoor
Akshay Kapoor

Reputation: 1

Extract data from XML stored as a CLOB

I need help with extracting the data stored in a CLOB column. I would like to convert the CLOB data into structured columns and rows.

CLOB column data:

<questions version="1">
<fld code="ADV" vis="Y"><oriVal></oriVal><curVal>N</curVal></fld>
<fld code="ASEN1" vis="Y"><oriVal></oriVal><curVal>N</curVal></fld>
<fld code="COGN" vis="Y"><oriVal></oriVal><curVal>A</curVal></fld>
<fld code="`enter code here`COM" vis="Y"><oriVal></oriVal></fld>
<fld code="COMORBID" vis="Y"><oriVal></oriVal><curVal>1</curVal></fld>
<fld code="DIET" vis="Y"><oriVal></oriVal><curVal>R</curVal></fld>
<fld code="DIS" vis="Y"><oriVal></oriVal></fld>
<fld code="DISC" vis="Y"><oriVal></oriVal><curVal></curVal></fld>
<fld code="DISC1" vis="Y"><oriVal></oriVal><curVal></curVal></fld>
<fld code="DISC2" vis="Y"><oriVal></oriVal><curVal></curVal></fld>
<fld code="DNR" vis="Y"><oriVal></oriVal><curVal>N</curVal></fld>
<fld code="ER" vis="Y"><oriVal></oriVal><curVal>1</curVal></fld>
</questions>

I would like to see the data as 

CODE        VALUE (curVal)
----------  --------------
ADV         N
ASEN1       N
COGN        Y
COMORBID    1

I tried running multiple queries but was not able to achieve the desired result.

SELECT EXTRACTVALUE(xmltype(QUESTIONXML), '/questions/fld/oriVal/curVal[@code="ADV"]') 
FROM pac_copy.questionnaire;

SELECT EXTRACTVALUE(QUESTIONXML, '/questions/fld') FROM pac_copy.questionnaire;

SELECT XMLTYPE(u.QUESTIONXML).EXTRACT('/questions/fld/text()') as SORTCODE 
FROM pac_copy.questionnaire  u;

Upvotes: 0

Views: 152

Answers (1)

Alex Poole
Alex Poole

Reputation: 191520

You can use XMLTable() for this:

select x.code, x.curval
from questionnaire q
cross join xmltable('/questions/fld'
  passing xmltype(q.questionxml)
  columns code varchar2(10) path '@code',
    curval varchar2(10) path '/curVal'
) x;

CODE       CURVAL   
---------- ----------
ADV        N         
ASEN1      N         
COGN       A         
COM                  
COMORBID   1         
DIET       R         
DIS                  
DISC                 
DISC1                
DISC2                
DNR        N         
ER         1

You can filter if you want to restrict to certain codes, or only those with values set; with a CTE to provide your string value:

with questionnaire (questionxml) as (
  select '<questions version="1">
<fld code="ADV" vis="Y"><oriVal></oriVal><curVal>N</curVal></fld>
<fld code="ASEN1" vis="Y"><oriVal></oriVal><curVal>N</curVal></fld>
<fld code="COGN" vis="Y"><oriVal></oriVal><curVal>A</curVal></fld>
<fld code="COM" vis="Y"><oriVal></oriVal></fld>
<fld code="COMORBID" vis="Y"><oriVal></oriVal><curVal>1</curVal></fld>
<fld code="DIET" vis="Y"><oriVal></oriVal><curVal>R</curVal></fld>
<fld code="DIS" vis="Y"><oriVal></oriVal></fld>
<fld code="DISC" vis="Y"><oriVal></oriVal><curVal></curVal></fld>
<fld code="DISC1" vis="Y"><oriVal></oriVal><curVal></curVal></fld>
<fld code="DISC2" vis="Y"><oriVal></oriVal><curVal></curVal></fld>
<fld code="DNR" vis="Y"><oriVal></oriVal><curVal>N</curVal></fld>
<fld code="ER" vis="Y"><oriVal></oriVal><curVal>1</curVal></fld>
</questions>' from dual
)
select x.code, x.curval
from questionnaire q
cross join xmltable('/questions/fld'
  passing xmltype(q.questionxml)
  columns code varchar2(10) path '@code',
    curval varchar2(10) path '/curVal'
) x
where x.curval is not null;

CODE       CURVAL   
---------- ----------
ADV        N         
ASEN1      N         
COGN       A         
COMORBID   1         
DIET       R         
DNR        N         
ER         1

Read more about using XQuery with XML data.

Upvotes: 0

Related Questions