I need to extract the CompletedDate from the Oracle CC&B table CI_FA. This data is stored in the clob (FA_DATA_AREA). The Query below contains several attempts at extracting this data. Note that only the last attempt is NOT commented out.

SELECT CI_FA.FA_ID,
CI_FA.FA_TYPE_CD,
CI_FA.SP_ID,
CI_FA.SCHED_DTTM,
CI_FA.FA_STATUS_FLG,
--EXTRACTVALUE(xmltype(FA_DATA_AREA), '//FieldOrderNotes/CompletedDate')
--EXTRACTVALUE(FA_DATA_AREA), ('/FieldOrderNotes/CompletedDate')
--EXTRACT(XMLTYPE(FA_DATA_AREA),'/FieldOrderNotes/CompletedDate')
--xmltype (FA_DATA_AREA) ('/FieldOrderNotes/CompletedDate()').getstringVal() "CompletedDate"
FA_DATA."CompletedDate"
FROM CI_FA
, XMLTABLE('/FA_DATA_AREA'
--PASSING CI_FA
COLUMNS
"CompletedDate" VARCHAR2(8) PATH '/FieldOrderNotes/CompletedDate')
FA_DATA
WHERE FA_STATUS_FLG = 'C'

Here is the error I get from the last attempt.

ORA-19228: XPST0008 - undeclared identifier: prefix '.' local-name ''
19228. 00000 - "XPST0008 - undeclared identifier: prefix '%s' local-name '%s'"
*Cause: The given identifier refers to either a type name, function name, namespace prefix, or variable name that is not defined in the static context.
*Action: Fix the expression to remove the identifier, or declare the appropriate variable, type, function or namespace.
Error at Line: 11 Column: 6

Here is a snap shot of the FA_DATA_AREA
<FieldOrderNotes><completionFlags><isMeterChanged>false</isMeterChanged><isRegisterChanged>false</isRegisterChanged><isMIUChanged>false</isMIUChanged><isTurnedOff>true</isTurnedOff><isTurnedOn>false</isTurnedOn><isMeterRead>false</isMeterRead></completionFlags><Employee>Harris, Chris</Employee><CompletedDate>7/10/2015 10:35:47 AM</CompletedDate><Field><label>Operated C/S-FAV*</label><value>Turned On at C/S</value></Field><Field><label>Packed Up*</label><value>Yes</value></Field><Field><label>Reading*</label><value>123356</value></Field></FieldOrderNotes>

<FieldOrderNotes><completionFlags><isMeterChanged>false</isMeterChanged><isRegisterChanged>false</isRegisterChanged><isMIUChanged>false</isMIUChanged><isTurnedOff>false</isTurnedOff><isTurnedOn>false</isTurnedOn><isMeterRead>true</isMeterRead></completionFlags><Employee>Harris, Chris</Employee><CompletedDate>7/10/2015 10:35:47 AM</CompletedDate><Field><label>Operated C/S-FAV*</label><value>Turned On at C/S</value></Field><Field><label>Packed Up*</label><value>Yes</value></Field><Field><label>Reading*</label><value>123356</value></Field></FieldOrderNotes>
<FieldOrderNotes><completionFlags><isMeterChanged>false</isMeterChanged><isRegisterChanged>false</isRegisterChanged><isMIUChanged>false</isMIUChanged><isTurnedOff>false</isTurnedOff><isTurnedOn>false</isTurnedOn><isMeterRead>true</isMeterRead></completionFlags><Employee>Harris, Chris</Employee><CompletedDate>7/10/2015 10:35:47 AM</CompletedDate><Field><label>Operated C/S-FAV*</label><value>Turned On at C/S</value></Field><Field><label>Packed Up*</label><value>Yes</value></Field><Field><label>Reading*</label><value>123356</value></Field></FieldOrderNotes>
<FieldOrderNotes><Employee>Harris, Chris</Employee><CompletedDate>7/10/2015 10:35:47 AM</CompletedDate><completionFlags><isMeterChanged>false</isMeterChanged><isRegisterChanged>false</isRegisterChanged><isMIUChanged>false</isMIUChanged><isTurnedOff>false</isTurnedOff><isTurnedOn>false</isTurnedOn><isMeterRead>true</isMeterRead></completionFlags><Field><label>Operated C/S-FAV*</label><value>Turned On at C/S</value></Field><Field><label>Packed Up*</label><value>Yes</value></Field><Field><label>Reading*</label><value>123356</value></Field></FieldOrderNotes>
<FieldOrderNotes><Employee>Harris, Chris</Employee><CompletedDate>7/10/2015 10:35:47 AM</CompletedDate><completionFlags><isMeterChanged>false</isMeterChanged><isRegisterChanged>false</isRegisterChanged><isMIUChanged>false</isMIUChanged><isTurnedOff>false</isTurnedOff><isTurnedOn>false</isTurnedOn><isMeterRead>true</isMeterRead></completionFlags><Field><label>Operated C/S-FAV*</label><value>Turned On at C/S</value></Field><Field><label>Packed Up*</label><value>Yes</value></Field><Field><label>Reading*</label><value>123356</value></Field></FieldOrderNotes>
<FieldOrderNotes><completionFlags><isMeterChanged>false</isMeterChanged><isRegisterChanged>false</isRegisterChanged><isMIUChanged>false</isMIUChanged><isTurnedOff>false</isTurnedOff><isTurnedOn>false</isTurnedOn><isMeterRead>false</isMeterRead></completionFlags></FieldOrderNotes>


Any assistance is appreciated.

SQL> create table ci_fa ( pk int, x xmltype );

Table created.

SQL>
SQL> insert into ci_fa values (1,
  2  xmltype('<FieldOrderNotes><completionFlags><isMeterChanged>false</isMeterChanged><isRegisterChanged>false</isRegisterChanged><isMIUChan
ged>false</isMIUChanged><isTurnedOff>true</isTurnedOff><isTurnedOn>false</isTurnedOn><isMeterRead>false</isMeterRead></completionFlags><Empl
oyee>Harris, Chris</Employee><CompletedDate>7/10/2015 10:35:47 AM</CompletedDate><Field><label>Operated C/S-FAV*</label><value>Turned On at
C/S</value></Field><Field><label>Packed Up*</label><value>Yes</value></Field><Field><label>Reading*</label><value>123356</value></Field></Fi
eldOrderNotes>'));

1 row created.

SQL>
SQL> SELECT pk, to_date(comp_date,'dd/mm/yyyy hh:mi:ss AM') completion_date
  2  FROM CI_FA c
  3      ,XMLTABLE('$p/FieldOrderNotes' passing c.x as "p"
  4  COLUMNS
  5        comp_date        varchar(30) path 'CompletedDate'
  6        ) v1;

        PK COMPLETION_DATE
---------- --------------------
         1 07-oct-2015 10:35:47

SQL> SELECT pk, to_date(comp_date,'dd/mm/yyyy hh:mi:ss AM') completion_date, meter_changed, register_changed, miu_changed
  2  FROM CI_FA c
  3      ,XMLTABLE('$p/FieldOrderNotes' passing c.x as "p"
  4  COLUMNS
  5        comp_date        varchar(30) path 'CompletedDate',
  6        completionFlags  xmltype path 'completionFlags'
  7        ) v1,
  8        XMLTABLE('completionFlags' passing v1.completionFlags
  9        COLUMNS
 10              meter_changed    varchar(20) path 'isMeterChanged',
 11              register_changed varchar(20) path 'isRegisterChanged',
 12              miu_changed      varchar(20) path 'isMIUChanged'
 13        ) v2;

        PK COMPLETION_DATE      METER_CHANGED        REGISTER_CHANGED     MIU_CHANGED
---------- -------------------- -------------------- -------------------- --------------------
         1 07-oct-2015 10:35:47 false                false                false

SQL>
SQL>
SQL>