Export Oracle Table Data by DBMS_XMLGEN -> How to Import?
I can easy export table data by using DBMS_XMLGEN. But is there a package to reimport this XML?
create table foo( id number ,text varchar2(30) ) / insert into foo values (1,'hello'); insert into foo values (2,'world'); declare l_foo_xml Clob; begin l_foo_xml := DBMS_XMLGEN.GETXML('select * from foo'); delete from foo; --- ???? insert the xml into foo ??? end; /
Have you looked at DBMS_XMLSAVE?
The Oracle documentation doesn't give examples of it's use, so a quick google will show you.
Here is something based on your example. (Which was inspired by the information from here)
create table foo( id number ,text varchar2(30) ) CREATE OR REPLACE PROCEDURE p(p_xml IN CLOB, p_table_name IN VARCHAR2) IS l_context DBMS_XMLSAVE.CTXTYPE; l_rows NUMBER; BEGIN l_context := DBMS_XMLSAVE.NEWCONTEXT(p_table_name); l_rows := DBMS_XMLSAVE.INSERTXML(l_context, p_xml); DBMS_XMLSAVE.CLOSECONTEXT(l_context); END; /
Call the procedure p with some sample xml
DECLARE l_xml CLOB; BEGIN l_xml := '<ROWSET> <ROW num="1"> <ID>123</ID> <TEXT>Some Text</TEXT> </ROW> </ROWSET>'; p(p_xml => l_xml, p_table_name => 'FOO'); END; /
Query the table
select * from foo