ora-00904 xml invalid identifier

If you have a XML column as part of a table, for example:

 DESC tab_with_xml_column
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -------------
 ID                                                             NUMBER
 XML_DATA                                                       XMLTYPE
 CREATED_DATE                                                   DATE

Then when trying to apply XML functionality to that column errors can occur, eg:

SELECT xml_data.EXTRACT('/userdetails/username') FROM tab_with_xml_column
       *
ERROR at line 1:
ORA-00904: "XML_DATA"."EXTRACT": invalid identifier

The solution is to simply enclose the column name within brackets:

SELECT (xml_data).EXTRACT('/userdetails/username') FROM tab_with_xml_column ;

(XML_DATA).EXTRACT('/USERDETAILS/USERNAME')
----------------------------------------------------------------------------------------------------
Fred

Example 2:

SQL > SELECT *
        FROM tab_with_xml_column
       WHERE (xml_data).EXTRACT('/userdetails/username/text()').GETSTRINGVAL()  = 'Fred';

                ID
------------------
XML_DATA
----------------------------------------------------------------------------------------------------
CREATED_D
---------
                 1

   Fred
   pass123

20-SEP-15

This was tested on Oracle 10 and Oracle 11. The table and data used was:

CREATE TABLE tab_with_xml_column
(id               NUMBER ,
 xml_data         XMLTYPE ,
 created_date     DATE
)
/

INSERT INTO tab_with_xml_column
(id ,
 xml_data ,
 created_date)
VALUES 
(1,
 XMLTYPE('
   Fred
   pass123
') ,
SYSDATE) 
/

COMMIT
/