SQL & PL/SQL

Hi All,

As per the earlier post,I am able to parse now.

i have also another concern as per below xml file.

My requirement is to identify perticular node ,whose having PriorValue attribute present in <pi:the Actual_Comp_Change> tag,those record should return.



<?xml version="1.0" encoding="UTF-8"?>
<pi:Extract_Employees xmlns:pi="urn:com.workday/picof">
<pi:Employee>
<pi:Employee_ID>1100</pi:Employee_ID>
<pi:Name>Surana</pi:Name>
<pi:Additional_Information>
<pi:Job_Title pi:PriorValue="">Intern - Master�s</pi:Job_Title>
<pi:Grade pi:PriorValue="">A</pi:Grade>
<pi:Actual_Comp_Change pi:PriorValue="0">500000</pi:Actual_Comp_Change>
</pi:Additional_Information>
</pi:Employee>
<pi:Employee>
<pi:Employee_ID>1000</pi:Employee_ID>
<pi:Name>roy</pi:Name>
<pi:Additional_Information>
<pi:Job_Title pi:PriorValue="">Intern - Master�s</pi:Job_Title>
<pi:Grade pi:PriorValue="">B</pi:Grade>
<pi:Actual_Comp_Change>216000</pi:Actual_Comp_Change>
</pi:Additional_Information>
<pi:Additional_Information>
<pi:Job_Title pi:PriorValue="">Intern - Master�s</pi:Job_Title>
<pi:Grade pi:PriorValue="">00</pi:Grade>
<pi:Actual_Comp_Change>266000</pi:Actual_Comp_Change>
</pi:Additional_Information>
</pi:Employee>
</pi:Extract_Employees>

---------------------------------------------------------

SELECT x.Employee_ID,x.Name,y.Job_Title,y.Grade,
y.Actual_Comp_Change

FROM
XMLTable(XMLNamespaces('urn:com.workday/picof' as "pi" ), '/pi:Extract_Employees/pi:Employee'
passing xmltype(dbms_xslprocessor.read2clob('XMLDIR', 'emp_new.xml', nls_charset_id('AL32UTF8') ))
columns Employee_ID varchar2(100) path '//pi:Employee/pi:Employee_ID' ,
Name varchar2(100) path '//pi:Employee/pi:Name' ,
Additional_Information xmltype path './pi:Additional_Information'

)x ,
xmltable(XMLNamespaces('urn:com.workday/picof' as "pi" ),'/pi:Additional_Information'
passing x.Additional_Information
columns Job_Title varchar2(100) path '//pi:Additional_Information/pi:Job_Title' ,
Grade varchar2(100) path '//pi:Additional_Information/pi:Grade' ,
Actual_Comp_Change varchar2(100) path '//pi:Additional_Information/pi:Actual_Comp_Change'


) y

--where existsNode(value(y), '//pi:Additional_Information/pi:Actual_Comp_Change/@pi:PriorValue') = 1;


OUTPUT:
EmployeeID_ Name JobTitle_ Grade ActualComp_Change_
1100 Surana Intern - Master¿s A 500000
1000 roy Intern - Master¿s B 216000
1000 roy Intern - Master¿s 00 266000

But my requirement is to display only those employeeID ,where Actual_Comp_Change tag having PriorValue attribute.
The required OutPUT should be :
EmployeeID_ Name JobTitle_ Grade ActualComp_Change_
1100 Surana Intern - Master¿s A 500000

is there any possibility to use ExistNode() function to the above quer or is there any alternative solution.
Please help me for above concern.

Thanks and regards,
Prasanta