Insert multiple rows via XML?

[Brian Marasca]

I know that Oracle has extensive support for XML, but I can't seem to find
any documentation on using an XML document to do multiple inserts into a
relational table. Note, I'm *not* trying to insert an XML document into an
XmlType column. I just want to construct an XML document containing the
values for many rows, and then use this document to insert all of the rows
with a single INSERT command. I want to do this for performance, because
doing many individual INSERTs in a loop in a stored procedure yields
unacceptable performance.

If anyone can help, I'd be very grateful.

Brian

[Pratap]

Pipelined function can be used to insert the data in one go. This is
one example from Oracle's site

DROP TABLE T;

CREATE TABLE T (
poname varchar2(20),
postreet varchar2(20),
pocity varchar2(20),
postate char(2),
pozip char(10)
);

DROP function poExplode_func;
DROP TYPE poRow_list;
DROP TYPE poRow_type;
create type poRow_type as object
(
poname varchar2(20),
postreet varchar2(20),
pocity varchar2(20),
postate char(2),
pozip char(10)
);
/

create type poRow_list as TABLE of poRow_type;
/

create function poExplode_func (arg IN sys.XMLType) return poRow_list
pipelined is
out_rec poRow_type;
poxml sys.XMLType;
i binary_integer := 1;
argnew sys.XMLType := arg;
begin
out_rec := poRow_Type(NULL,NULL,NULL,NULL,NULL);
loop
-- extract the i'th purchase order!
poxml := argnew.extract('//PO['||i||']');
exit when poxml is null;
-- extract the required attributes..!!!
out_rec.poname := poxml.extract('/PO/PONAME/text()').getStringVal();
--out_rec.poname := argnew.extract('/POLIST/PO['||i||']/PONAME/text()').getStringVal();
out_rec.postreet := poxml.extract('PO/POADDR/STREET/text()').getStringVal();
out_rec.pocity := poxml.extract('//POADDR/CITY/text()').getStringVal();
out_rec.postate := poxml.extract('//POADDR/STATE/text()').getStringVal();
out_rec.pozip := poxml.extract('//POADDR/ZIP/text()').getStringVal();
PIPE ROW(out_rec);
i := i + 1;
end loop;
return;
end;
/

declare
s1 clob;
begin
s1 := '<PO>
<PONAME>Po_1</PONAME>
<POADDR>
<STREET>100 Main Street</STREET>
<CITY>Sunnyvale</CITY>
<STATE>CA</STATE>
<ZIP>94086</ZIP>
</POADDR>
</PO>';
s1 := s1||s1||s1||s1||s1||s1||s1||s1||s1||s1;
s1 := s1||s1||s1||s1||s1||s1||s1||s1||s1||s1;
--s1 := s1||s1||s1||s1||s1;
INSERT INTO T
select *
from TABLE( CAST(
poExplode_func(
sys.XMLType.createXML(
'<?xml version="1.0"?>
<POLIST>'||s1||'</POLIST>')
) AS poRow_list));
end;
/

select * from T;
Pratap
Cognizant Technology Solutions, India