How can I delete rows in an oracle table from xml input parameter?

Question

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

Answer 1

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