I mentioned table structure,insert statement and select statement for table.I am not able to fetch the values for the codehighlighted in bold colour. Please see my below select sql statement.
<A P="Vessel Name" V="Air"/> .here Vessel Name denotes column name in output and Air denotes it's value.
<A P="Entry Port" V="Miami"/>here Entry Port denotes column name in output and Miami denotes it's value.
table structure:
CREATE TABLE EMPLOYEES
(
data XMLTYPE
);
Insert statement for table:
INSERT INTO EMPLOYEES
VALUES (xmltype ('<CEEMISFILE>
<CLAIM CLAIMSTATUS="O" INTIMATIONDATE="12/11/2015 00:00:00" LOSSDATE="12/11/2015 00:00:00" LOSSCAUSE="Y" EVENTCODE="" REVISIONNUMBER="2" REVISIONDATE="01/08/2016 01:42:08">
<INSUREDITEMATTRIBUTE>
<A P="Vessel Name" V="Air"/>
<A P="Entry Port" V="Miami"/>
<A P="Invoice Value (US$)" V="19983.88"/>
<A P="Date of B/L" V="12/05/2015 00:00:00"/>
<A P="Exit Port" V="Santiago"/>
<A P="Insured Value(US $)" V="27588.44"/>
<A P="Additional Serial No" V=""/>
<A P="Mode of transport" V="C"/>
<A P="Loss Type" V=""/>
<A P="Bill of Lading No" V="ALX158120871"/>
<A P="Transit Risk" V="Voyage"/>
<A P="Cover Code" V="OC"/>
<A P="Goods Insured" V="C"/>
<A P="Equipment Model" V="Spare Parts"/>
<A P="Carrier 1" V="Lan Linera Aerea"/>
<A P="Equipment Serial No" V=""/>
<A P="Additional Model No" V=""/>
<A P="Location of Loss" V="Santiago"/>
<A P="Part Damaged" V="SHTGE"/>
</INSUREDITEMATTRIBUTE>
<CLAIMATTRIBUTE>
<A P="Claim Amount" V="0"/>
<A P="Loss" V="0"/>
<A P="Date Report Filed" V="01/08/2016 00:00:00"/>
<A P="CAT Ship Facility" V="56"/>
<A P="Claim Reserve" V="1474.61"/>
<A P="Risk/Division" V=""/>
<A P="CISC Claim Number" V=""/>
<A P="Salvage" V="0"/>
<A P="Date Acknowledged" V="01/07/2016 00:00:00"/>
<A P="Date Claim Filed Closed" V=""/>
<A P="Estimated Recovery" V="0"/>
<A P="Estimated Recovery Fee" V="0"/>
<A P="TP Expenses" V="0"/>
<A P="CEEMIS Claim Status" V="2"/>
<A P="Freight Forwarder" V="Alexim Trading Corp."/>
<A P="Bordereaux Number" V=""/>
<A P="Carrier 2" V=""/>
<A P="Dealer/Assured" V="Finning Chile SA"/>
<A P="Adjuster Code" V=""/>
<A P="Survey Fee" V="0"/>
<A P="AI Marine Claim Number" V=""/>
<A P="Caterpillar Division" V="Y50Z"/>
<A P="Estimated Loss" V="N/A"/>
<A P="Alt Ref- CEEMIS" V=""/>
<A P="Subrogation Code" V=""/>
<A P="Remarks" V=""/>
<A P="Sale Terms" V="DAF"/>
<A P="Insurer Policy Number" V="MASICNY0001US15"/>
<A P="Total" V="0"/>
<A P="Certificate Number" V="3611089358667"/>
<A P="Funds Received" V="0"/>
<A P="Settling Fee" V="0"/>
<A P="Deductible Amount" V="0"/>
<A P="Office Handling" V="Cox Europe"/>
<A P="Gross Recovery" V=""/>
<A P="Recovery Fee" V=""/>
<A P="CEEMIS Claim Number" V="206162"/>
<A P="Subrogation Status" V="False"/>
</CLAIMATTRIBUTE>
</CLAIM>
</CEEMISFILE>'));
--
select statement:
SELECT x.*
FROM employees t,
XMLTABLE ('/CEEMISFILE/CLAIM' PASSING t.data COLUMNS type VARCHAR2(30) PATH '@CLAIMSTATUS',
INTIMATIONDATE VARCHAR2(30) PATH '@INTIMATIONDATE',
LOSSDATE VARCHAR2(30) PATH '@LOSSDATE',
LOSSCAUSE VARCHAR2(30) PATH '@LOSSCAUSE',
EVENTCODE VARCHAR2(30) PATH '@EVENTCODE',
REVISIONNUMBER VARCHAR2(30) PATH '@REVISIONNUMBER',
REVISIONDATE VARCHAR2(30) PATH '@REVISIONDATE',
"Vessel Name" VARCHAR2 (30) PATH '/CEEMISFILE/CLAIM/CLAIMATTRIBUTE/A/@P',
"Entry Port" VARCHAR2 (30) PATH '/CEEMISFILE/CLAIM/CLAIMATTRIBUTE/A/@V') x