create table people ( personid varchar2(10) primary key, name varchar2(20), address varchar2(60), tel varchar2(20), fax varchar2(20), email varchar2(40) ); create or replace procedure domsample ( dir varchar2, infile varchar2 ) is p xmlparser.Parser; doc xmldom.DOMDocument; procedure getElements(doc xmldom.DOMDocument) is nt1 xmldom.DOMNodeList; nt2 xmldom.DOMNodeList; dn1 xmldom.DOMNode; dn2 xmldom.DOMNode; dnnm xmldom.DOMNamedNodeMap; len1 number; len2 number; sqlstr varchar2(200); begin nt1 := xmldom.getElementsByTagName(doc,'PERSON'); len1 := xmldom.getLength(nt1); for i in 0..len1-1 loop sqlstr := 'insert into people values('; dn1 := xmldom.item(nt1,i); if xmldom.getNodeName(dn1) = 'PERSON' then dnnm := xmldom.getAttributes(dn1); dn2 := xmldom.item(dnnm,0); sqlstr := sqlstr || '''' || xmldom.getNodeValue(dn2)||''''; end if; nt2 := xmldom.getChildNodes(dn1); len2 := xmldom.getLength(nt2); for j in 0..len2-1 loop dn1 := xmldom.item(nt2,j); sqlstr := sqlstr || ',''' || xmldom.getNodeValue(xmldom.getfirstChild(dn1)) ||''''; end loop; sqlstr := sqlstr ||')'; execute immediate(sqlstr); dbms_output.put_line(sqlstr); end loop; end getElements; begin p := xmlparser.newParser; xmlparser.setValidationMode(p,false); xmlparser.setBaseDir(p,dir); xmlparser.parse(p,dir || '/' || infile); doc := xmlparser.getDocument(p); getElements(doc); xmldom.freeDocument(doc); end domsample; begin domsample('D:/XMLDB','people.xml'); end; select * from people; |