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

Thanks Christian

Answers


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


Need Your Help

Python: My function will print continuous outputs but will not return them.

python function continuous

First of all I could not figure out an appropriate title for this. Secondly, I am pretty new to programming. Anyway, I have this function:

Mono WebForms: Setting default page to run when starting debugging

asp.net mono webforms xsp

is it possible to set a default page to run when starting debugging? In Visual Studio you can set the default page either through the context menu in Solution Explorer or in the project properties....

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.