Use extract function and xpath function to retrieve xml element data

SQL> CREATE TABLE R22106(
       id  NUMBER PRIMARY KEY,
       emps XMLType NOT NULL
                         );

Table created.


SQL> INSERT INTO R22106 VALUES (1, xmltype('<?xml version="1.0" standalone="no"
 ?>
    <emps>
       <emp>
            <home_address>address 1</home_address>
        </emp>
    </emps>')
    );

1 row created.


SQL> select extract(emps, '/emps/emp/home_address/text()' ) "Xml Element Value"
    from R22106;

  


Xml Element Value
address 1