I am using following procedure which takes a XML parameter along with other parameters.
PROCEDURE SetProdMaint (p_validity IN VARCHAR2,
p_products IN VARCHAR2 XML,
p_user IN VARCHAR2)
The XML data (p_products) is of the form as follows.
<?xml version="1.0"?>
<products>
<product id="123">
<reason>No maintenance</reason>
</product>
<product id="456">
<reason>Sold by customer</reason>
</product>
<product id="789">
<reason>bought</reason>
</product>
</products>
Within SetProdMaint () procedure, I need to run a loop around the XML data for each record set and pick out the product id and reason for each row. I need to do some insert/update/delete operations on a oracle table using these product id and reason values. That table does not have a XML field. So I need to store these values in PLSQL variables and then play with them within PLSQL.
The other solution might be storing each row of id and reason in a 2 dimension PLSQL table from the XML data. Then I can easily play around the PLSQL table easily.
I am new to XML with PLSQL.
Please someone suggest me a method to solve my problem.