Hi,
I am stuck with a scenario where i need to check if a tag in a XML exists. This tag is down below by two xml sequence levels (Organization->Project(level1)->Employee(level2), when i use existsNode function i am getting a value of '1' for all the records .
Not sure if a cross join is the reason for the display of the same value for all the rows?
XML_SAMPLE is a table with one column of XMLType
Please suggest me a way to get the desired output,
if this can be acheived using some other functions or method please feel free to comment on the same
Desired Output
*==================*
Last Field is the........IS_MANAGER
XEHONZ Pvt ltd BSE0902345 ISO0000289 Level-4 Test_Project_1 Fixed Bid D1 Ronnie 0
XEHONZ Pvt ltd BSE0902345 ISO0000289 Level-4 Test_Project_1 Fixed Bid D2 Mily 0
XEHONZ Pvt ltd BSE0902345 ISO0000289 Level-4 Test_Project_1 Fixed Bid SM1 Meena *1*
XEHONZ Pvt ltd BSE0902345 ISO0000289 Level-4 Test_Project_2 Under Proposal BA1 Vivek 0
XEHONZ Pvt ltd BSE0902345 ISO0000289 Level-4 Test_Project_2 Under Proposal BA2 Jonah 0
XEHONZ Pvt ltd BSE0902345 ISO0000289 Level-4 Test_Project_2 Under Proposal SM1 Ankur *1*
I am getting all the IS_MAGER values as *1*
Here is the query
*=================*
SELECT
m.ORG_NAME,
m.BSE_ID,
m.ISO_NUMBER,
m.CMMI,
l.PROJ_NAME,
l.CONTRACT_TYPE,
k.EMP_ID,
k.EMP_NAME,
--trying with two types of exisNode starting from root element and from the employee fragment both doesnt work for me
*existsNode(l.employee_fragment ,'/employee/Management') IS_MANAGER_1,
existsNode(OBJECT_VALUE ,'/Organization/Project/employee/Management') IS_MANAGER_2*
from XML_SAMPLE,
xmltable(
'$XS/Organization' passing OBJECT_VALUE as "XS"
columns
ORG_NAME VARCHAR2(100) PATH 'Name',
BSE_ID VARCHAR2(100) PATH 'BSE_ID',
ISO_NUMBER VARCHAR2(100) PATH 'ISO',
CMMI VARCHAR2(100) PATH 'CMMI',
project_fragment XMLTYPE PATH 'Project'
) m,
xmltable(
'$PJ/Project' passing m.project_fragment as "PJ"
columns
PROJ_NAME VARCHAR2(100) PATH '@Name',
CONTRACT_TYPE VARCHAR2(100) PATH 'ContractType',
employee_fragment XMLTYPE PATH 'employee'
) l,
xmltable(
'$EY/employee' passing l.employee_fragment as "EY"
columns
EMP_ID VARCHAR2(100) PATH '@id',
EMP_NAME VARCHAR2(100) PATH 'name'
) k;
Here is the sample XML
*======================*
<?xml version="1.0" ?>
<Organization>
<Name>XEHONZ Pvt ltd</Name>
<BSE_ID>BSE0902345</BSE_ID>
<ISO>ISO0000289</ISO>
<CMMI>Level-4</CMMI>
<Project Name="Test_Project_1">
<ProjId>P0001</ProjId>
<ContractType>Fixed Bid</ContractType>
<employee id="D1">
<name>Ronnie</name>
<age>28</age>
</employee>
<employee id="D2">
<name>Mily</name>
<age>27</age>
</employee>
<employee id="SM1">
<name>Meena</name>
<age>36</age>
*<Management Roll="Senior Manager">Manages Teams</Management>*
</employee>
</Project>
<Project Name="Test_Project_2">
<ProjId>P0002</ProjId>
<ContractType>Under Proposal</ContractType>
<employee id="BA1">
<name>Vivek</name>
<age>28</age>
</employee>
<employee id="BA2">
<name>Jonah</name>
<age>27</age>
</employee>
<employee id="SM1">
<name>Ankur</name>
<age>39</age>
*<Management Roll="Senior Manager">Manages Teams</Management>*
</employee>
</Project>
</Organization>
Regards,
Arghyadip