> CREATE OR REPLACE PROCEDURE PRC_OPEN_PERIOD > ( InputXml in VARCHAR2,Tmode in varchar2,pOUT OUT number ) > IS > v_count NUMBER(38) := 1; > v_inner NUMBER(38) := 1; > str varchar(20); > l_xml xmltype; > BEGIN > IF Tmode ='DELETE' THEN l_xml=xmltype(InputXml); > DELETE FROM TBL_MST_OPEN_PERIOD > WHERE month = l_xml.extract('//openperiod/month/text()').getStringVal() > and > village_id in (select village_id from tbl_mst_open_period > where > village_id in > l_xml.extract('//openperiod/village/villageID/text()').getStringVal()) > ; > pOUT :=1; > COMMIT; END IF; END;
STRUCTURE OF THE INPUT XML:
<root> <openperiod> <month></month> <village> <villageID></villageID> <villageID></villageID> </village> </openperiod> </root>
I have a table named
tbl_mst_open_period with columns
I want to delete rows from the table by extracting the values from the xml input parameter. But the data is not getting deleted. Can anyone help??
P.S. When i replace the extarct function by hardcoded values, the delete is working. Also I tried the same xml in an insert into the table by extracting. It was working then. Also is the delete statement actually correct??