Oracle 存储过程实现数据库导入导出xml文件

实例：Oracle导出导入XML,准确来说是服务器端的导入导出,客户端的话需要事先通过应用程序传送到服务器端目录下..一般应用的话是不会在服务器端进行解析的,除非是DBA操作,作为学习看了解下,以下通过测试,测试环境ORACLE9I版本号,9.0.2.1创建测试表:CREATETABLEPEOPLE(PERSONIDVARCHAR2(10)PRIMARYKEY,NAMEVARCHAR2(20),ADDRESSVARCHAR2(60),TELVARCHAR2(20),FAXVARCHAR2(20),EMAILVARCHAR2(40));XML文件数据,保存为people.xml,放置于D盘下的TEST目录下:?xmlversion=1.0?PEOPLEPERSONPERSONID=E01NAMETonyBlair/NAMEADDRESS10DowningStreet,London,UK/ADDRESSTEL(061)98765/TELFAX(061)98768/[e-mail]/EMAIL/PERSONPERSONPERSONID=E02NAMEBillClinton/NAMEADDRESSWhiteHouse,USA/ADDRESSTEL(001)640098765/TELFAX(001)640098769/[e-mail]/EMAIL/PERSONPERSONPERSONID=E03NAMETomCruise/NAMEADDRESS57JumboStreet,NewYork,USA/ADDRESSTEL(001)450067859/TELFAX(001)450067895/[e-mail]/EMAIL/PERSONPERSONPERSONID=E04NAMELindaGoodman/NAMEADDRESS78CraxLane,London,UK/ADDRESSTEL(061)5456789/TELFAX(061)5456772/[e-mail]/EMAIL/PERSON/PEOPLE导入XML文件到数据表中的存储过程:CREATEORREPLACEPROCEDUREaddPerson(file_pathVARCHAR2,log_pathVARCHAR2)AS--//XML解析器xmlParXMLPARSER.parser:=XMLPARSER.NEWPARSER;--//DOM文档对象docxmldom.DOMDocument;leninteger;personNodesxmldom.DOMNodeList;chilNodesxmldom.DOMNodeList;tempNodexmldom.DOMNode;tempArrMapxmldom.DOMNamedNodeMap;--================================--以下变量用于获取XML节点的值pidvarchar2(4);namevarchar2(50);addressvarchar2(200);telvarchar2(20);faxvarchar2(20);emailvarchar(100);tmpinteger;--================================BEGINxmlPar:=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元素FORiin0..len-1LOOP--获取第i个PERSONtempNode:=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)));--插入数据INSERTINTOPEOPLEVALUES(pid,name,address,tel,fax,email);COMMIT;ENDLOOP;--释放文档对象xmldom.freeDocument(doc);EXCEPTIONWHENOTHERSTHENDBMS_output.PUT_LINE(SQLERRM);ENDaddPerson;导出的存储过程:输入导出路径就可以了,下面是用来嵌套存储过程：createorreplaceproceduredb2xml(xmlfilevarchar2)asdocxmldom.DOMDocument;retxmldom.DOMNode;peoplenodexmldom.DOMNode;--遍历整个people表的游标Cursorcur_peopleisselect*frompeople;--将people表中一行记录转换为元素--并插入到DOM文档对像根结点PEOPLE下的过程procedureaddperson(docxmldom.DOMDocument,peoplexmldom.DOMNode,v_pidvarchar2,v_namevarchar2,v_addrvarchar2,v_telvarchar2,v_faxvarchar2,v_emailvarchar2)ispersonelemxmldom.DOMElement;personnodexmldom.DOMNode;itemelemxmldom.DOMElement;itemnodexmldom.DOMNode;textxmldom.DOMText;begin--创建PERSON结点personelem:=xmldom.createElement(doc,'PERSON');--设置PERSONID属性xmldom.setAttribute(personelem,'PERSONID',v_pid);personnode:=xmldom.appendChild(peoplenode,xmldom.makeNode(personelem));--向PERSON结点中添加NAME元素itemelem:=xmldom.createElement(doc,'NAME');--将NAME结点添加到PERSON结点中itemnode:=xmldom.appendChild(personnode,xmldom.makeNode(itemelem));--创建文本结点text:=xmldom.createTextNode(doc,v_name);--将文本结点添加到NAME结点下，以构成完整NAME元素itemnode:=xmldom.appendChild(itemnode,xmldom.makeNode(text));--向PERSON结点中添加ADDRESS元素itemelem:=xmldom.createElement(doc,'ADDRESS');itemnode:=xmldom.appendChild(personnode,xmldom.makeNode(itemelem));text:=xmldom.createTextNode(doc,v_addr);itemnode:=xmldom.appendChild(itemnode,xmldom.makeNode(text));--向PERSON结点中添加TEL元素itemelem:=xmldom.createElement(doc,'TEL');itemnode:=xmldom.appendChild(personnode,xmldom.makeNode(itemelem));text:=xmldom.createTextNode(doc,v_tel);itemnode:=xmldom.appendChild(itemnode,xmldom.makeNode(text));--向PERSON结点中添加FAX元素itemelem:=xmldom.createElement(doc,'FAX');itemnode:=xmldom.appendChild(personnode,xmldom.makeNode(itemelem));text:=xmldom.createTextNode(doc,v_fax);itemnode:=xmldom.appendChild(itemnode,xmldom.makeNode(text));--向PERSON结点中添加EMAIL元素itemelem:=xmldom.createElement(doc,'EMAIL');itemnode:=xmldom.appendChild(personnode,xmldom.makeNode(itemelem));text:=xmldom.createTextNode(doc,v_email);itemnode:=xmldom.appendChild(itemnode,xmldom.makeNode(text));endaddperson;begin--创建一个新DOM文档对像doc:=xmldom.newDOMDocument;--为文档添加根结点PEOPLEpeoplenode:=xmldom.makeNode(xmldom.createElement(doc,'PEOPLE'));ret:=xmldom.appendChild(xmldom.makeNode(doc),peoplenode);--使用游标遍历people中的每行，生成每一行对应的PERSON元素并添加到PEOPLE根结点中forv_rowincur_peopleloopaddperson(doc,peoplenode,v_row.personid,v_row.name,v_row.address,v_row.tel,v_row.fax,v_row.email);endloop;--将结果写入