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;
/
Thanks Christian
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
