[nitin.manipal]

Hi,

I have XML file. Which i am trying to read in PL/SQL procedure. i am not able to retrieve node value. i am able browse till node name exactly but when i uses xmldom.getnodevalue(). it doesnt fetch any value.

XML FILE -->

<?xml version="1.0" encoding="utf-8" ?>
<ReportCriteria>
	<Header>
		<CarNumber>CP 123456</CarNumber>
		<BuilReBuild></BuilReBuild>
		<Load>Yes</Load>
		<MishapDate>01-Oct-2008</MishapDate>
		<Mileage>120</Mileage>
		<Responsiblity>1</Responsiblity>
		<Location>Coquitlam</Location>
		<Sub-Division>Coquitlam</Sub-Division>
		<Cause></Cause>
		<Status></Status>
		<AtScene>Yes</AtScene>
	</Header>
	<Criteria>
		<Criterion>
			<JobCode>1944</JobCode>
			<JobDesc>BRAKE WHEEL,NON GEARED BRAKE</JobDesc>
			<Location>C</Location>
			<CC>2</CC>
			<Qualifier>AA</Qualifier>
			<Rule72Desc></Rule72Desc>
			<Rule72-US></Rule72-US>
			<Quantity>1</Quantity>
		</Criterion>
		<Criterion>
			<JobCode>1988</JobCode>
			<JobDesc>PULLROD CLEVIS</JobDesc>
			<Location>C</Location>
			<CC>7</CC>
			<Qualifier></Qualifier>
			<Rule72Desc></Rule72Desc>
			<Rule72-US></Rule72-US>
			<Quantity>1</Quantity>
		</Criterion>
		<Criterion>
			<JobCode>2358</JobCode>
			<JobDesc>COUPLER YOKE, Y 45 HTE</JobDesc>
			<Location>C</Location>
			<CC>2</CC>
			<Qualifier></Qualifier>
			<Rule72Desc></Rule72Desc>
			<Rule72-US></Rule72-US>
			<Quantity>1</Quantity>
		</Criterion>
	</Criteria>
</ReportCriteria>

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

Procedure ---->

CREATE OR REPLACE procedure WEAPP.WE_SP_Estimate(in_xml IN varchar2,refcur IN OUT DailyRPTPkg.DailyRPTCur)
IS

parser              sys.xmlparser.Parser;
doc                 sys.xmldom.DOMDocument;
nodeLst             sys.xmldom.DOMNodeList;
XMLen               number;
node                xmldom.DOMNode;
job_code_node       xmldom.DOMNode;
Qty_node            xmldom.DOMNode;
job_code_value      varchar2(50);
Qty_Value           number;


WE_SP_Estimate_Tab WE_SP_Estimate_TABLE;

vCarNumber          VARCHAR2(50);
vLocations          VARCHAR2(100);
vJobCode            VARCHAR2(50);
vJobCode_desc       VARCHAR2(200);
vQuantity           NUMBER;
vMishap_Date        date;
vCuase              varchar2(200);
vStatus             varchar2(50);
vMileage            varchar2(50);
vSub_div            varchar2(50);
vResponsibility     varchar2(100);
vLoad               varchar2(10);
vAt_Scene           varchar2(10);
vCC                 number;

vAAR_MTRL_PRICE_AMT  NUMBER(6,2);
vAAR_CREDIT_AMT      NUMBER(6,2);
vVRBL_HOUR_QTY       NUMBER(6,3);
vAAR_JobPrice        NUMBER(6,2);
vAAR_JobRate         NUMBER(6,2);
vFIXED_HOUR_QTY      NUMBER(6,3);

vFRA_JobPrice        NUMBER(6,2);
vFRA_CREDIT_AMT      NUMBER(6,2);
vFRA_MTRL_PRICE_AMT  NUMBER(6,2);
vFRA_JobRate         NUMBER(6,2);

iIndex               binary_integer := 0;



Begin


WE_SP_Estimate_Tab := WE_SP_Estimate_TABLE();


parser := sys.xmlparser.newParser;
sys.xmlparser.setValidationMode (parser, FALSE);
sys.xmlparser.parseBuffer (parser, in_xml);
doc := sys.xmlparser.getDocument (parser);
nodeLst := sys.xmldom.GETELEMENTSBYTAGNAME(doc,'Criterion');
--nodeLst := sys.xmldom.GETELEMENTSBYTAGNAME(doc,'*');
XMLen   := sys.xmldom.GETLENGTH(nodeLst);

dbms_output.put_line('XMLen-->' || XMLen);
 
 


for iIndex in 0..XMLen-1 loop

node := xmldom.item(nodeLst,0);
job_code_node:= xmldom.GETFIRSTCHILD(node);

Qty_node := xmldom.GETLASTCHILD(node);
 
--xmldom.GetNODETYPE(job_code_node);
job_code_value := xmldom.GETNODEVALUE(node);

--if xmldom.getNodeType(job_code_node) = xmldom.TEXT_NODE then

Qty_value := to_number(xmldom.getnodevalue(Qty_node));

dbms_output.put_line('node name-->' || xmldom.GETNODENAME(node));
dbms_output.put_line('child 1 node name-->' || xmldom.GETNODENAME(job_code_node));
dbms_output.put_line('child 2 node name-->' || xmldom.GETNODENAME(Qty_node));

dbms_output.put_line('job_code_value-->'|| xmldom.GETNODEVALUE(job_code_node));


dbms_output.put_line('TEST -->' || job_code_value || ' ' || Qty_value);

Qty_Value := 20;

Select AAR_CREDIT_AMT,AAR_MTRL_PRICE_AMT,FRA_MTRL_PRICE_AMT,FRA_CREDIT_AMT,VRBL_HOUR_QTY,FIXED_HOUR_QTY 
INTO   vAAR_CREDIT_AMT,vAAR_MTRL_PRICE_AMT,vFRA_MTRL_PRICE_AMT,vFRA_CREDIT_AMT,vVRBL_HOUR_QTY ,vFIXED_HOUR_QTY
FROM   WEAPP.WE_PRICE_MATRIX
WHERE  JOB_CD = job_code_value;

dbms_output.put_line(vAAR_CREDIT_AMT || ' ' || vAAR_MTRL_PRICE_AMT);

vAAR_JobPrice :=  ((vAAR_MTRL_PRICE_AMT - vAAR_CREDIT_AMT) * Qty_value) + 
                    (((vVRBL_HOUR_QTY  * Qty_value) + vFIXED_HOUR_QTY) * vAAR_JobRate);
                    

dbms_output.put_line('AAR_Job Price = ' || vAAR_JobPrice );


vFRA_JobPrice :=  ((vFRA_MTRL_PRICE_AMT - vFRA_CREDIT_AMT) * Qty_value) + 
                    (((vVRBL_HOUR_QTY  * Qty_value) + vFIXED_HOUR_QTY) * vAAR_JobRate);
                    

dbms_output.put_line('FRA_Job Price = ' || vFRA_JobPrice);
                    
                    
End loop;



END;
/

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

Can any body suggest what mistake i am doing...

Thanks,
Nitin