Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
> 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??

share|improve this question
    
Why dont you insert your xml into a temp table and then use a query. –  jclozano Jan 16 at 15:15
    
you mean to say like an xml table? –  Subhaprasad Mukherjee Jan 17 at 18:01
    
Either a XML Table or creating a table and inserting the xml after to finally make a delete ... where –  jclozano Jan 21 at 16:16
add comment

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
    
thank you immensely vincent. i will keep that in mind. –  Subhaprasad Mukherjee Jan 17 at 17:36
    
also i realized that since i am already getting the villageID s to be deleted from the xml input parameters there is absolutely no need to run that select village_id sub query. –  Subhaprasad Mukherjee Jan 17 at 18:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.