oracle解析xml数据_oracle 解析xml xmldom.getnodevalue-CSDN博客

本文链接：https://blog.csdn.net/yeyue4113/article/details/1890400

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;