Ribeiro Ribeiro - 5 months ago 56
PHP Question

Oracle Insert Data into XML Type Table

I have a table with a XMLTYPE column, and i want to insert data into it in XML Format, but the information does not come in XML format from the server. I was thinking about implementing a stored procedure who takes the values from the server and Inserts the data in the XML Format into that column, but the procedure is giving me the right parenthesis error and i have no idea why.

Here's the code:

CREATE TABLE xml_tab (
id NUMBER,
xml_data XMLTYPE
);
ALTER TABLE XML_TAB
MODIFY (ID DEFAULT XML_TAB_SEQ.NEXTVAL);


And the stored procedure that i'm trying to implement.

CREATE OR REPLACE PROCEDURE insert_xml_proc (
dish_name IN VARCHAR,
dish_type IN VARCHAR,
dish_image IN VARCHAR ) AS
BEGIN
INSERT INTO XML_TAB (XML_DATA) values(
xmltype(
'<xml>
<item>
<DISH_ID>'XML_TAB_SEQ.CURRVAL'</DISH_ID>
<DISH_NAME>'dish_name'</DISH_NAME>
<DISH_TYPE>'dish_type'</DISH_TYPE>
<DISH_IMAGE>'dish_image'</DISH_IMAGE>
</item></xml>' ));
END;


The idea is that i use my form in PHP to send some variables into a stored procedure to insert this data. Inserting into a regular table and then fetching the data from it to insert as XML into the XML Table would also work, but the problem remains the same.

Thank you !

Answer

It's because you're not concatenating properly. It should be:

CREATE OR REPLACE PROCEDURE insert_xml_proc (
dish_name IN VARCHAR2,
dish_type IN VARCHAR2,
dish_image IN VARCHAR2 ) AS
BEGIN
INSERT INTO XML_TAB (XML_DATA) values(
xmltype(
'<xml>
<item>
    <DISH_ID>'||XML_TAB_SEQ.CURRVAL||'</DISH_ID>
    <DISH_NAME>'||dish_name||'</DISH_NAME>
    <DISH_TYPE>'||dish_type||'</DISH_TYPE>
    <DISH_IMAGE>'||dish_image||'</DISH_IMAGE>
</item></xml>' ));
END;
/

N.B. Note also that I've converted your parameters from VARCHAR to VARCHAR2

There are better ways of generating xml, though, e.g.:

with sample_data as (select 'a' dish_name, 'b' dish_type, 'c' dish_image from dual)
select xmlelement("xml", xmlelement("item",
                                    xmlforest(OLD_SEQ.nextval as dish_id,
                                              dish_name,
                                              dish_type,
                                              dish_image))) xmldata
from  sample_data;

XMLDATA
------------------------------------------------------------------------------------------------------------------------
<xml><item><DISH_ID>262</DISH_ID><DISH_NAME>a</DISH_NAME><DISH_TYPE>b</DISH_TYPE><DISH_IMAGE>c</DISH_IMAGE></item></xml>