要在oracle 10g解析一个XML解析,怎么解析?请大家帮忙提点意见,万分感谢!
解决方案 »
- db_block_checking和db_block_checksum区别
- oracle日期类型问题
- Oracle 10g企业版(1个CPU的无限用户) 是什么意思?
- 如何查询出表1中有但表2中没有的人
- 急问:XML入ORACLE库的问题 分不够尽管说
- Oracle安装在D盘上,C盘上的win2000重新安装了,我想数据库文件还在D盘上,重新安装2000和Oracle后能找回数据库吗?
- 急oracle倒入遇到的问题:(在线等!~~
- 请教 为什么数据库会越来越慢
- oracle9i的补丁问题,版本9.0.1.4
- Linux服务器安装Oracle
- oracle的CBO优化器主要是根据什么来选择表的连接方式的?
- OEM
例子如下:
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>[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>
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;
ERROR at line 50: PLS-00103: Encountered the symbol "/"
3. P XMLPARSER.PARSER;
4. DOC XMLDOM.DOMDOCUMENT;
5. PROCEDURE READELEMENTS(DOC XMLDOM.DOMDOCUMENT) IS
6. NL XMLDOM.DOMNODELIST;
7. LEN NUMBER;
我把"/"改掉后,又出现了这样的错误:
ERROR at line 51: PLS-00103: Encountered the symbol "BEGIN"
3. P XMLPARSER.PARSER;
4. DOC XMLDOM.DOMDOCUMENT;
5. PROCEDURE READELEMENTS(DOC XMLDOM.DOMDOCUMENT) IS
6. NL XMLDOM.DOMNODELIST;
7. LEN NUMBER;
这是怎么回事呢?要是用IF-THEN-ELSE的话怎么做呢?
XMLPARSER.PARSE(P, DIR ||'/' ||INPFILE);
改成XMLPARSER.PARSE(P, DIR ||'\' ||INPFILE);
把emp.xml 放到C盘根目录就行,
测试通过..