需要解析的xml文件:people.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>新建表:
1 CREATE TABLE PEOPLE
2 (
3 PERSONID VARCHAR2(4) PRIMARY KEY,
4 NAME VARCHAR2(50),
5 ADDRESS VARCHAR2(200),
6 TEL VARCHAR2(20),
7 FAX VARCHAR2(20),
8 EMAIL VARCHAR2(100)
9 );解析并持久化的存储过程代码:
1 /**
2 *file_path 需要解析的XML文件路径 如:C:\people.xml
3 *log_path 保存日志的文件的路径 如: C:\people.txt
4 **/ CREATE OR REPLACE PROCEDURE addPerson_pp(file_path VARCHAR2,log_path VARCHAR2)
AS
--//XML解析器
xmlPar XMLPARSER.parser := XMLPARSER.NEWPARSER;
--//DOM文档对象
doc xmldom.DOMDocument;
len integer;
personNodes xmldom.DOMNodeList;
chilNodes xmldom.DOMNodeList;
tempNode xmldom.DOMNode;
tempArrMap xmldom.DOMNamedNodeMap;
--================================
--以下变量用于获取XML节点的值
pid varchar2(4);
name varchar2(50);
address varchar2(200);
tel varchar2(20);
fax varchar2(20);
email varchar(100);
tmp integer;
--================================
BEGIN
dbms_output.put_line(file_path||'......xmlPath');
xmlPar := xmlparser.newParser;
xmlparser.setErrorLog( xmlPar, log_path);
xmlparser.parse(xmlPar, file_path);
doc := xmlparser.getDocument( xmlPar );
-- 释放解析器实例
xmlparser.freeParser(xmlPar);
-- 获取所有PERSON元素
personNodes := xmldom.getElementsByTagName( doc, 'PERSON' );
len := xmldom.getLength( personNodes );
--遍历所有PERSON元素
FOR i in 0..len-1
LOOP
--获取第i个PERSON
tempNode := xmldom.item( personNodes, i );
--所有属性
tempArrMap := xmldom.getAttributes(tempNode);
--获取PERSONID的值
pid := xmldom.getNodeValue(xmldom.getNamedItem(tempArrMap,'PERSONID'));
--获取子元素的值
chilNodes := xmldom.getChildNodes(tempNode);
tmp := xmldom.GETLENGTH( chilNodes );
name := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 0 )));
address := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 1 )));
tel := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 2 )));
fax := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 3 )));
email := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 4 )));
--插入数据
INSERT INTO PEOPLE VALUES (pid,name,address,tel,fax,email);
COMMIT;
END LOOP;
-- 释放文档对象
xmldom.freeDocument(doc);
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
END addPerson_pp;=====================================================================
然后,,我在Command Window 里调用这个存储过程。。再查询表,,,里面没记录。。我不知道错在哪里了。
请各位高手看看帮助解决解决。
SQL> EXEC addPerson_pp('C:\people.xml','people.txt');
PL/SQL procedure successfully completed
SQL> select name from PEOPLE;
NAME
--------------------------------------------------
<?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>新建表:
1 CREATE TABLE PEOPLE
2 (
3 PERSONID VARCHAR2(4) PRIMARY KEY,
4 NAME VARCHAR2(50),
5 ADDRESS VARCHAR2(200),
6 TEL VARCHAR2(20),
7 FAX VARCHAR2(20),
8 EMAIL VARCHAR2(100)
9 );解析并持久化的存储过程代码:
1 /**
2 *file_path 需要解析的XML文件路径 如:C:\people.xml
3 *log_path 保存日志的文件的路径 如: C:\people.txt
4 **/ CREATE OR REPLACE PROCEDURE addPerson_pp(file_path VARCHAR2,log_path VARCHAR2)
AS
--//XML解析器
xmlPar XMLPARSER.parser := XMLPARSER.NEWPARSER;
--//DOM文档对象
doc xmldom.DOMDocument;
len integer;
personNodes xmldom.DOMNodeList;
chilNodes xmldom.DOMNodeList;
tempNode xmldom.DOMNode;
tempArrMap xmldom.DOMNamedNodeMap;
--================================
--以下变量用于获取XML节点的值
pid varchar2(4);
name varchar2(50);
address varchar2(200);
tel varchar2(20);
fax varchar2(20);
email varchar(100);
tmp integer;
--================================
BEGIN
dbms_output.put_line(file_path||'......xmlPath');
xmlPar := xmlparser.newParser;
xmlparser.setErrorLog( xmlPar, log_path);
xmlparser.parse(xmlPar, file_path);
doc := xmlparser.getDocument( xmlPar );
-- 释放解析器实例
xmlparser.freeParser(xmlPar);
-- 获取所有PERSON元素
personNodes := xmldom.getElementsByTagName( doc, 'PERSON' );
len := xmldom.getLength( personNodes );
--遍历所有PERSON元素
FOR i in 0..len-1
LOOP
--获取第i个PERSON
tempNode := xmldom.item( personNodes, i );
--所有属性
tempArrMap := xmldom.getAttributes(tempNode);
--获取PERSONID的值
pid := xmldom.getNodeValue(xmldom.getNamedItem(tempArrMap,'PERSONID'));
--获取子元素的值
chilNodes := xmldom.getChildNodes(tempNode);
tmp := xmldom.GETLENGTH( chilNodes );
name := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 0 )));
address := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 1 )));
tel := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 2 )));
fax := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 3 )));
email := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 4 )));
--插入数据
INSERT INTO PEOPLE VALUES (pid,name,address,tel,fax,email);
COMMIT;
END LOOP;
-- 释放文档对象
xmldom.freeDocument(doc);
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
END addPerson_pp;=====================================================================
然后,,我在Command Window 里调用这个存储过程。。再查询表,,,里面没记录。。我不知道错在哪里了。
请各位高手看看帮助解决解决。
SQL> EXEC addPerson_pp('C:\people.xml','people.txt');
PL/SQL procedure successfully completed
SQL> select name from PEOPLE;
NAME
--------------------------------------------------
SQL> EXEC addPerson_pp('C:\people.xml','people.txt');
C:\people.xml......xmlPath
ORA-31020: The operation is not allowed, Reason: Not supported
PL/SQL procedure successfully completed
SQL>
报了一个错
操作不允计,原因:不支持。
....为什么。??????要怎么解决这个问题呢
http://forums.oracle.com/forums/thread.jspa?messageID=2404213