日期:2014-05-17 浏览次数:20930 次
例子如下: CREATE DIRECTORY test AS 'c:\'; GRANT WRITE ,READ ON DIRECTORY test TO lgt; emp.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>blair@everywhere.com </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>bill@everywhere.com </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>cruise@everywhere.com </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>linda@everywhere.com </EMAIL> </PERSON> </PEOPLE> CREATE TABLE PEOPLE ( PERSONID VARCHAR2(10), NAME VARCHAR2(20), ADDRESS VARCHAR2(60), TEL VARCHAR2(20), FAX VARCHAR2(20), EMAIL VARCHAR2(40) ); CREATE OR REPLACE PROCEDURE XML2TABLE(DIR VARCHAR2, INPFILE VARCHAR2 ) IS P XMLPARSER.PARSER; DOC XMLDOM.DOMDOCUMENT; PROCEDURE READELEMENTS(DOC XMLDOM.DOMDOCUMENT) IS NL XMLDOM.DOMNODELIST; LEN NUMBER; N XMLDOM.DOMNODE; ATTN XMLDOM.DOMNODE; NNM XMLDOM.DOMNAMEDNODEMAP; NL2 XMLDOM.DOMNODELIST; LEN2 NUMBER; STRSQL VARCHAR2(1000); BEGIN NL := XMLDOM.GETELEMENTSBYTAGNAME(DOC, 'PERSON'); LEN := XMLDOM.GETLENGTH(NL); FOR I IN 0 .. LEN - 1 LOOP STRSQL := 'INSERT INTO PEOPLE VALUES ('; N := XMLDOM.ITEM(NL, I); IF XMLDOM.GETNODENAME(N) = 'PERSON' THEN NNM := XMLDOM.GETATTRIBUTES(N); ATTN := XMLDOM.ITEM(NNM, 0); STRSQL := STRSQL || '''' || XMLDOM.GETNODEVALUE(ATTN) || ''''; END IF; NL2 := XMLDOM.GETCHILDNODES(N); LEN2 := XMLDOM.GETLENGTH(NL2); FOR J IN 0 .. LEN2 - 1 LOOP N := XMLDOM.ITEM(NL2, J); STRSQL := STRSQL || ', ''' || XMLDOM.GETNODEVALUE(XMLDOM.GETFIRSTCHILD(N)) || ''''; END LOOP; STRSQL := STRSQL || ')'; -- dbms_output.put_line(strSQL); EXECUTE IMMEDIATE (STRSQL); END LOOP; COMMIT; DBMS_OUTPUT.PUT_LINE(''); END READELEMENTS; BEGIN P := XMLPARSER.NEWPARSER; XMLPARSER.SETVALIDATIONMODE(P, FALSE); XMLPARSER.SETBASEDIR(P, DIR); XMLPARSER.PARSE(P, DIR ||'/' ||INPFILE); DOC := XMLPARSER.GETDOCUMENT(P); XMLPARSER.FREEPARSER(P); DBMS_OUTPUT.PUT_LINE('读取文文件元素并保存到表 PEOPLE 中.'); READELEMENTS(DOC); XMLDOM.FREEDOCUMENT(DOC); END XML2TABLE; / BEGIN XML2TABLE('TEST', 'emp.xml'); END; / select *from perople;
------解决方案--------------------