Hi Team,
I have studied about DBMS_XMLSTORE and tried out for my requirement. Link in Docs: https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb_dbmstore.htm
Requirement is to get an XML as Input for Stored Procedure and Insert Into a table.
Below is the my code. When I test, If I pass the value as CLOB in input window then NO Operation takes place. But if I change the Procedure to initialize the variable with the XML value then it is working. In the Oracle Docs its mentioned to use CLOB only. Please help.
I am using PLSQL Developer to pass the input
create or replace procedure prc_test_collection (i_value IN clob)
as
l_ctx dbms_xmlstore.ctxtype;
l_rows NUMBER;
l_xml clob;
BEGIN
l_xml := i_value;
l_ctx := dbms_xmlstore.newcontext('t_test_collection'); -- Get saved context
dbms_xmlstore.clearUpdateColumnList(l_ctx); -- Clear the update settings
-- Set the columns to be updated/inserted as a list of values
dbms_xmlstore.setUpdateColumn(l_ctx, 'SPND_ID');
dbms_xmlstore.setUpdateColumn(l_ctx, 'DIV_CD');
dbms_xmlstore.setUpdateColumn(l_ctx, 'CNTRY_CD');
-- Set ROW tag
dbms_xmlstore.setRowTag(l_ctx, 'SPENDREC');
-- Insert the doc.
l_rows := DBMS_XMLSTORE.insertXML(l_ctx, l_xml);
DBMS_OUTPUT.put_line(l_rows || ' rows inserted.');
commit;
-- Close the context
DBMS_XMLSTORE.closeContext(l_ctx);
end;
If I change the l_xml assignation as below then it works.
l_xml := '<DOCUMENTELEMENT>
<SPENDREC>
<SPND_ID>EMP-1</SPND_ID>
<DIV_CD>EMP1-DIV-1</DIV_CD>
<CNTRY_CD>EMP1-CNTRY-1</CNTRY_CD>
</SPENDREC>
<SPENDREC>
<SPND_ID>EMP-2</SPND_ID>
<DIV_CD>EMP2-DIV-2</DIV_CD>
<CNTRY_CD>EMP1-CNTRY-2</CNTRY_CD>
</SPENDREC>
</DOCUMENTELEMENT>';