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

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