Re: Insert multiple rows via XML?

From: Pratap <pratap_fin_at_rediffmail.com>
Date: 10 Aug 2004 22:41:56 -0700
Message-ID: <830861d2.0408102141.38b4d589_at_posting.google.com>


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 Received on Wed Aug 11 2004 - 07:41:56 CEST

Original text of this message