DevOps, CI/CD and Automation

Hi All,

I am facing problem when trying to access xml data from CLOB.
My stored procedure is as below:

CREATE OR REPLACE PROCEDURE usp_XMLTEST2

(

in_XmlDoc IN CLOB

)

IS

BEGIN



UPDATE XML_TEST

SET SETFLAG='N'

WHERE (ITEMCODE,WAREHOUSE) IN

(



SELECT

TO_NUMBER(EXTRACT(COLUMN_VALUE,'//Entry/@ItemCode')) ,

TO_NUMBER(EXTRACT(COLUMN_VALUE,'//Entry/@Warehouse'))

FROM TABLE(XMLSEQUENCE( EXTRACT(XMLTYPE(in_XmlDoc), '//List/*' )))

);

END usp_XMLTEST2;





Exec usp_XMLTEST2('<List Daemon="2"><Entry ItemCode="112333" Warehouse="4101" /><Entry ItemCode="112333" Warehouse="4103" /></List>');



It gives error:

ERROR at line 1:

ORA-22905: cannot access rows from a non-nested table item

ORA-06512: at "PUBLIX.USP_XMLTEST2", line 8

ORA-06512: at line 1




Please devise solution to this problem.

Regards
Nitin Bajaj