Oracle表数据和xml文件间相互导入导出的PL.docVIP

在Oracle表数据和xml文件间相互导入导出的PL/SQL 过程 将已知格式xml文件中的数据导入Oracle数据库、把数据库中的数据导出成xml文件的过程。已知格式xml文件如下： ?xml?version=1.0?PEOPLE????PERSON?PERSONID=E01????????NAMETony?Blair/NAME????????ADDRESS10?Downing?Street,?London,?UK/ADDRESS????????TEL(061)?98765/TELFAX(061)?98768/FAX????????EMAILblair@/EMAIL????/PERSON????PERSON?PERSONID=E02????????NAMEBill?Clinton/NAME????????ADDRESSWhite?House,?USA/ADDRESS????????TEL(001)?6400?98765/TELFAX(001)?6400?98769/FAX????????EMAILbill@/EMAIL????/PERSON????PERSON?PERSONID=E03????????NAMETom?Cruise/NAME????????ADDRESS57?Jumbo?Street,?New?York,?USA/ADDRESS????????TEL(001)?4500?67859/TELFAX(001)?4500?67895/FAX????????EMAILcruise@/EMAIL????/PERSON????PERSON?PERSONID=E04????????NAMELinda?Goodman/NAME????????ADDRESS78?Crax?Lane,?London,?UK/ADDRESS????????TEL(061)?54?56789/TELFAX(061)?54?56772/FAX????????EMAILlinda@/EMAIL????/PERSON/PEOPLE 于是写出过程如下： --建表CREATE?TABLE?PEOPLE(???PERSONID?VARCHAR2(10)?PRIMARY?KEY,???NAME?VARCHAR2(20),???ADDRESS?VARCHAR2(60),???TEL??VARCHAR2(20),???FAX??VARCHAR2(20),???EMAIL?VARCHAR2(40));--从xml文件载入数据保存到数据库的过程?create?or?replace?procedure?xml2db(xmlfile?varchar2)?as??p?xmlparser.Parser;??doc?xmldom.DOMDocument;??n?xmldom.DOMNode;??nl?xmldom.DOMNodeList;??len?number;??--根据PERSON结点生成插入SQL语句的过程???function?insertsql(node?xmldom.DOMNode)?return?varchar2??is????n?xmldom.DOMNode;????nl?xmldom.DOMNodeList;????len?number;????nnm?xmldom.DOMNamedNodeMap;????isql?varchar2(200);??begin????--取得PERSON结点所有属性?????nnm?:=?xmldom.getAttributes(node);????isql?:=?insert?into?people?values(;????isql?:=?isql?||?;????--取得所有属性中的第一个属性，即PERSONID，并拼接到SQL语句中?????isql?:=?isql?||?xmldom.getNodeValue(xmldom.item(nnm,0));????isql?:=?isql?||?;????--取得PERSON结点下的所有结点，准备遍历?????nl?:=?xmldom.getChildNodes(node);????len?:=?xmldom.getLength(nl);????for?i?in?0..len-1?loop??????--取出第i个结点???????n?:=?xmldom.item(nl,i);??????isql?:=?isql?||?,;??????isql?:=?isql?||?;??????--将结点的文本值取出并拼接到SQL语句中??????i