Hi,
I have a table like below
create table emptable
( id number primary key, emps varchar2 ( 500 )) ;

with this data in it

insert into emptable values
(
1, '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><employee emp_no="100" emp_policy="fulltime" emp_id="100" emp_name="test"/>'
) ;

And I am able to select the attribute using '@'.Thanks to Chris for helping to me figure out this.

select emps.*
from emptable x,
xmltable('/employee'
passing xmltype(x.emps)
columns
emp_no varchar2(100) path '@emp_no',
emp_policy varchar2(100) path '@emp_policy',
emp_id varchar2(100) path '@emp_id',
emp_name varchar2(100) path '@emp_name'
) emps;

EMP_NO EMP_POLICY EMP_ID EMP_NAME
100 fulltime 100 test


This is what I am trying to do:
If the EMP_POLICY is fulltime i need to show as 'F' otherwise 'T'
And the out put should be - Expected output

EMP_NO EMP_POLICY EMP_ID EMP_NAME
100 F 100 test

I have tried to add case statement around like : - case emp_policy when 'fulltime' then 'F' (emp_policy varchar2(100) path '@emp_policy')
But its failing to select.What would be the best way to use case statement inside xml table?

create table emptable ( id number primary key, emps varchar2 ( 500 )) ;

insert into emptable values (
1, '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><employee emp_no="100" emp_policy="fulltime" emp_id="100" emp_name="test"/>'
) ;

select emps.no, case emp_policy when 'fulltime' then 'F' end policy
from emptable x,
xmltable('/employee'
passing xmltype(x.emps)
columns
emp_no varchar2(100) path '@emp_no',
emp_policy varchar2(100) path '@emp_policy',
emp_id varchar2(100) path '@emp_id',
emp_name varchar2(100) path '@emp_name'
) emps;

EMP_NO POLICY
100 F