How to delete rows in an oracle table from xml input parameter

oracleoracle-11g-r2xml

> 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 period_id number, month number, village_id number, created_by varchar(20), is_active varchar(1).

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??

Best Answer

The problem is your village subquery, it returns a single value of aggregated village ids instead of a table of ids:

SQL> WITH DATA AS
  2   (SELECT XMLTYPE('<root>
  3  <openperiod>
  4  <month></month>
  5  <village>
  6     <villageID>1</villageID>
  7     <villageID>2</villageID>
  8  </village>
  9  </openperiod>
 10  </root>') l_xml
 11      FROM dual)
 12  SELECT extract(l_xml,'//openperiod/village/villageID/text()').getStringVal()
 13    FROM DATA;
EXTRACT(L_XML,'//OPENPERIOD/VI
--------------------------------------------------------------------------------
12

Instead, you should use XMLTABLE (see this other SO question for example):

SQL> WITH DATA AS
  2   (SELECT XMLTYPE('<root>
  3  <openperiod>
  4  <month></month>
  5  <village>
  6     <villageID>1</villageID>
  7     <villageID>2</villageID>
  8  </village>
  9  </openperiod>
 10  </root>') l_xml
 11      FROM dual)
 12  SELECT x.id
 13    FROM DATA d,
 14         XMLTABLE('//openperiod/village/villageID'
 15                  PASSING d.l_xml
 16                  COLUMNS ID VARCHAR2(30) PATH 'text()') x;
ID
------------------------------
1
2

My advice when dealing with complex queries:

  • start with SQL, then when your query is sound and gives the good result, put it in PL/SQL
  • start with SELECT, not DML
  • decompose your query, make sure each subquery returns the appropriate result
  • build a small example with test tables with 1-2 rows each
Related Question