Extract data from oracle XML type columns

Question

This is how the XML within the table looks like

<?xml version="1.0" encoding="UTF-8"?>
<select multiselect="false" name="c_f_2441_select_dev" phiField="true" readOnly="false" title="select_dev">
  <dataValidationRule/>
  <CFData>false</CFData>
  <suppressRules>false</suppressRules>
  <options>
    <option code="1" status="A">Sel1</option>
    <option code="2" status="A">Sel2</option>
    <option code="3" status="A">Sel3</option>
  </options>
  <groups/>
</select>

Within the "options" element there are three values 'Sel1', 'Sel2' and 'Sel3'

select xt.* 
from  ST3_ENT1_REG4.custom_field cf,
      XMLTABLE('/select'
      PASSING cf.data_model_xml
         COLUMNS 
           options   VARCHAR2(20)  PATH 'options'
          ) xt
where field_name='c_f_2441_select_dev';

The above query concatenates the three values and gives me the output as "Sel1Sel2Sel3". Is there a way I can get those three values in three different row.

Thanks

MT0 · Accepted Answer · 2018-01-17 10:20:44Z

2

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE custom_field ( field_name, data_model_xml ) AS
SELECT
'c_f_2441_select_dev',
XMLTYPE( '<?xml version="1.0" encoding="UTF-8"?>
<select multiselect="false" name="c_f_2441_select_dev" phiField="true" readOnly="false" title="select_dev">
  <dataValidationRule/>
  <CFData>false</CFData>
  <suppressRules>false</suppressRules>
  <options>
    <option code="1" status="A">Sel1</option>
    <option code="2" status="A">Sel2</option>
    <option code="3" status="A">Sel3</option>
  </options>
  <groups/>
</select>' ) FROM DUAL;

Query 1:

If you just want the content of the 1st, 2nd and 3rd option element:

select xt.* 
from  custom_field cf,
      XMLTABLE('/select/options'
      PASSING cf.data_model_xml
         COLUMNS 
           option1   VARCHAR2(20)  PATH 'option[1]',
           option2   VARCHAR2(20)  PATH 'option[2]',
           option3   VARCHAR2(20)  PATH 'option[3]'
          ) xt
where field_name='c_f_2441_select_dev'

Results:

| OPTION1 | OPTION2 | OPTION3 |
|---------|---------|---------|
|    Sel1 |    Sel2 |    Sel3 |

Query 2:

If you want the text of the option element where the code attribute is 1, 2 and 3 then:

select xt.* 
from  custom_field cf,
      XMLTABLE('/select/options'
      PASSING cf.data_model_xml
         COLUMNS 
           option1   VARCHAR2(20)  PATH 'option[@code=1]',
           option2   VARCHAR2(20)  PATH 'option[@code=2]',
           option3   VARCHAR2(20)  PATH 'option[@code=3]'
          ) xt
where field_name='c_f_2441_select_dev'

Results:

| OPTION1 | OPTION2 | OPTION3 |
|---------|---------|---------|
|    Sel1 |    Sel2 |    Sel3 |

Query 3 - Update - in separate rows.:

select xt.* 
from  custom_field cf,
      XMLTABLE(
        '/select/options/option'
        PASSING cf.data_model_xml
        COLUMNS value VARCHAR2(20) PATH 'text()'
      ) xt
where field_name='c_f_2441_select_dev'

Results:

| VALUE |
|-------|
|  Sel1 |
|  Sel2 |
|  Sel3 |

edited Jan 17, 2018 at 10:20

answered Jan 17, 2018 at 9:42

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mallikarjuna siraguppi Over a year ago

I wanted the data to be populated in three different rows but under single column.

MT0 Over a year ago

@Mallikarjunasiraguppi Updated

Collectives™ on Stack Overflow

Extract data from oracle XML type columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related