PL/sql解析xml文件，并导入到Oracle中

Question

碰到这样个问题：
我需要写个procedure来解析一个xml文件，并且把对应的数据保存到oracle中。
下面是xml文件：
<?xml version="1.0"?>
<PEOPLE>
<PERSON PERSONID="E01">
<NAME>Tony Blair</NAME>
<ADDRESS>10 Downing Street, London, UK</ADDRESS>
<TEL>(061) 98765</TEL><FAX>(061) 98768</FAX>
<EMAIL>[email protected]</EMAIL>
</PERSON>
<PERSON PERSONID="E02">
<NAME>Bill Clinton</NAME>
<ADDRESS>White House, USA</ADDRESS>
<TEL>(001) 6400 98765</TEL><FAX>(001) 6400 98769</FAX>
<EMAIL>[email protected]</EMAIL>
</PERSON>
<PERSON PERSONID="E03">
<NAME>Tom Cruise</NAME>
<ADDRESS>57 Jumbo Street, New York, USA</ADDRESS>
<TEL>(001) 4500 67859</TEL><FAX>(001) 4500 67895</FAX>
<EMAIL>[email protected]</EMAIL>
</PERSON>
<PERSON PERSONID="E04">
<NAME>Linda Goodman</NAME>
<ADDRESS>78 Crax Lane, London, UK</ADDRESS>
<TEL>(061) 54 56789</TEL><FAX>(061) 54 56772</FAX>
<EMAIL>[email protected]</EMAIL>
</PERSON>
</PEOPLE>

Answer 1

以下是我写的匿名块SQL> declare
  2    dir    varchar2(50);
  3    infile varchar2(20);
  4    errlog varchar2(20);
  5    p      xmlparser.parser;
  6    doc    xmldom.DOMDocument;
  7    n1     xmldom.DOMNodeList;
  8    len1   number(3);
  9    n2     xmldom.DOMNodeList;
 10    len2   number(3);
 11    n3     xmldom.DOMNodeList;
 12    len3   number(3);
 13    v1      xmldom.DOMNode;
 14    v2  xmldom.DOMNode;
 15    v3 xmldom.DOMNode;
 16    attn      xmldom.DOMNode;
 17    strSQL varchar2(100);
 18    nnm    xmldom.DOMNamedNodeMap;
 19  begin
 20    dir    := 'F:\xml';
 21    infile := 'people.xml';
 22    errlog := 'err.log';
 23    p := xmlparser.newParser;
 24    xmlparser.setValidationMode(p, false);
 25    xmlparser.setErrorLog(p, dir || '\' || errlog);
 26    xmlparser.setBaseDir(p, dir);
 27    xmlparser.parse(p, dir || '\' || infile);
 28    doc := xmlparser.getDocument(p);
 29    xmlparser.freeParser(p);
 30    strSQL := 'INSERT INTO PEOPLE VALUES (';
 31    n1     := xmldom.getElementsByTagName(doc, 'PEOPLE');
 32    len1   := xmldom.getLength(n1);
 33    for i in 0 .. len1 - 1 loop
 34      v1    := xmldom.item(n1, i);
 35      n2   := xmldom.getChildNodes(v1);
 36      len2 := xmldom.getLength(n2);
 37      for j in 0 .. len2 - 1 loop
 38        v2 := xmldom.item(n2, j);
 39        if xmldom.getNodeName(v2) = 'PERSON' then
 40          nnm    := xmldom.getAttributes(v2);
 41          attn   := xmldom.item(nnm, 0);
 42          strSQL := strSQL || '''' || xmldom.getNodeValue(attn) || '''';
 43        end if;
 44        n3   := xmldom.getChildNodes(v2);
 45        len3 := xmldom.getLength(n3);
 46        for z in 0 .. len3 - 1 loop
 47          v3      := xmldom.item(n3, z);
 48          strSQL := strSQL || ', ''' ||
 49                    xmldom.getNodeValue(xmldom.getFirstChild(v3)) || '''';
 50        end loop;
 51        strSQL := strSQL || ')';
 52      end loop;
 53      --execute immediate (strSQL);
 54      --commit;
 55      dbms_output.put_line(strSQL);
 56    end loop;
 57    -- 释放文档对象
 58    xmldom.freeDocument(doc);
 59  end;
 60  /

Answer 2

报告的错误是：
ORA-06502: PL/SQL: 数字或值错误 
ORA-06512: 在line 48

Answer 3

请问
1、除了xmldom还有其他的解析方法吗？
2、为什么我的报告这个错误？我的table中的类型全部是varchar2、我也写过to_char()，但是还是报告同样的错误。
也请高手指点指点需要改进的地方。

Answer 4

执行了一下，没语法错误
估计是strSQL太短，加长长度试试

Answer 5

我给strSQL加长长度了换200，没用的。

Answer 6

pl/sql能解析xml文件可真的第一次见过，到低能否实现呢？

Answer 7

pl/sql完全可以解析xml的.
我也只是在看oracle9i的解析,着几天看到10g又增加了新的解析方法.
我现在已经可以把表导出成xml文件.
但是还是不知道我上面的代码错在什么地方了

Answer 8

感谢wiler(@_@) 
我明白你的意思了
我发现错误了,
1.我没有把strSQL清空.
2,循环写的也是有问题的.
现在我把正确的代码贴出来,供大家参考:
declare
  dir    varchar2(50);
  infile varchar2(20);
  errlog varchar2(20);
  p      xmlparser.parser;
  doc    xmldom.DOMDocument;
  n1     xmldom.DOMNodeList;
  len1   number(3);
  n2     xmldom.DOMNodeList;
  len2   number(3);
  n3     xmldom.DOMNodeList;
  len3   number(3);
  v1     xmldom.DOMNode;
  v2     xmldom.DOMNode;
  v3     xmldom.DOMNode;
  attn   xmldom.DOMNode;
  strSQL varchar2(200);
  nnm    xmldom.DOMNamedNodeMap;
begin
  dir    := 'F:\xml';
  infile := 'people.xml';
  errlog := 'err.log';
  p      := xmlparser.newParser;
  xmlparser.setValidationMode(p, false);
  xmlparser.setErrorLog(p, dir || '\' || errlog);
  xmlparser.setBaseDir(p, dir);
  xmlparser.parse(p, dir || '\' || infile);
  doc := xmlparser.getDocument(p);
  xmlparser.freeParser(p);
  n1     := xmldom.getElementsByTagName(doc, 'PEOPLE');
  len1   := xmldom.getLength(n1);
  for i in 0 .. len1 - 1 loop
    v1   := xmldom.item(n1, i);
    n2   := xmldom.getChildNodes(v1);
    len2 := xmldom.getLength(n2);
    for j in 0 .. len2 - 1 loop
        strSQL := 'INSERT INTO POPLE VALUES (';
      v2 := xmldom.item(n2, j);
      if xmldom.getNodeName(v2) = 'PERSON' then
        nnm    := xmldom.getAttributes(v2);
        attn   := xmldom.item(nnm, 0);
        strSQL := strSQL || '''' || to_char(xmldom.getNodeValue(attn)) || '''';
      end if;
      n3   := xmldom.getChildNodes(v2);
      len3 := xmldom.getLength(n3);
      for z in 0 .. len3 - 1 loop
        v3     := xmldom.item(n3, z);
        strSQL := strSQL || ', ''' ||
                  to_char(xmldom.getNodeValue(xmldom.getFirstChild(v3))) || '''';
      end loop;
      strSQL := strSQL || ')';
      dbms_output.put_line(strSQL);
      execute immediate(strSQL);
      commit;
      strSQL:='';
    end loop;
  end loop;
  -- 释放文档对象
  xmldom.freeDocument(doc);
end;
/

Answer 9

请注意strSQL伏值的地方,和strSQL:=''