How can I delete rows in an oracle table from xml input parameter?
The problem is your village subquery, it returns a single value of aggregated village ids instead of a table of ids:

SQL&gt; WITH DATA AS
  2   (SELECT XMLTYPE('&lt;root&gt;
  3  &lt;openperiod&gt;
  4  &lt;month&gt;&lt;/month&gt;
  5  &lt;village&gt;
  6     &lt;villageID&gt;1&lt;/villageID&gt;
  7     &lt;villageID&gt;2&lt;/villageID&gt;
  8  &lt;/village&gt;
  9  &lt;/openperiod&gt;
 10  &lt;/root&gt;') 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&gt; WITH DATA AS
  2   (SELECT XMLTYPE('&lt;root&gt;
  3  &lt;openperiod&gt;
  4  &lt;month&gt;&lt;/month&gt;
  5  &lt;village&gt;
  6     &lt;villageID&gt;1&lt;/villageID&gt;
  7     &lt;villageID&gt;2&lt;/villageID&gt;
  8  &lt;/village&gt;
  9  &lt;/openperiod&gt;
 10  &lt;/root&gt;') 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