我建了一个xml文件如下:放在“C:\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>在数据库中建表:
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 xml2table(dir varchar2, inpfile varchar2,
errfile varchar2) is
-- 參數說明:
-- dir 基本目錄,如 'd:\xml\plsql'
-- inpfile 輸入檔案名,不含路徑,如 'people.xml'
-- errfile 錯誤日誌檔,保存解析錯誤資訊, 如 'err.log'
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
-- 讀取 PERSON 元素
nl := xmldom.getElementsByTagName(doc, 'PERSON');
len := xmldom.getLength(nl);
-- 遍曆元素
for i in 0..len-1 loop
-- 構造動態 SQL 語句
strSQL := 'INSERT INTO PEOPLE VALUES (';
n := xmldom.item(nl, i);
if xmldom.getNodeName(n)='PERSON' then
nnm := xmldom.getAttributes(n); -- 讀取 PERSONID 屬性
attn := xmldom.item(nnm, 0);
strSQL := strSQL || '''' || xmldom.getNodeValue(attn) || '''';
end if;
-- 讀取 PERSON 的子節點
nl2 := xmldom.getChildNodes(n);
len2 := xmldom.getLength(nl2);
-- 處理 NAME, ADDRESS, ... 等節點
for j in 0..len2-1 loop
n := xmldom.item(nl2, j);
strSQL := strSQL || ', ''' || xmldom.getNodeValue(xmldom.getFirstChild(n)) || '''';
end loop;
-- 完成 動態 SQL 語句的構造
strSQL := strSQL || ')';
-- dbms_output.put_line(strSQL);
-- 執行插入記錄的 SQL 語句
execute immediate(strSQL); -- 執行動態 SQL
end loop;
commit; -- 提交插入
dbms_output.put_line('');
end readElements;
begin
-- 新建解析器實例
p := xmlparser.newParser;
-- 設置解析器特性
xmlparser.setValidationMode(p, FALSE);
-- xmlparser.setErrorLog(p, dir || '\' || errfile);
xmlparser.setBaseDir(p, dir);
-- 解析輸入檔
xmlparser.parse(p, dir || '\' || inpfile);
-- 獲取解析後的文檔對象
doc := xmlparser.getDocument(p);
-- 讀取文檔元素
dbms_output.put_line('讀取文檔元素並保存到表 PEOPLE 中.');
readElements(doc);
-- 釋放資源
xmldom.freeDocument(doc);
end xml2table;运行时老是提示:ora-20100: error occurred while parsing: no such file or directory.还有ora-06512等错误。請問:那位高手知道错误的原因啊?谢谢!
<?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) PRIMARY KEY,
NAME VARCHAR2(20),
ADDRESS VARCHAR2(60),
TEL VARCHAR2(20),
FAX VARCHAR2(20),
EMAIL VARCHAR2(40)
);我将xml文件导入数据库的代码如下:
create or replace procedure xml2table(dir varchar2, inpfile varchar2,
errfile varchar2) is
-- 參數說明:
-- dir 基本目錄,如 'd:\xml\plsql'
-- inpfile 輸入檔案名,不含路徑,如 'people.xml'
-- errfile 錯誤日誌檔,保存解析錯誤資訊, 如 'err.log'
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
-- 讀取 PERSON 元素
nl := xmldom.getElementsByTagName(doc, 'PERSON');
len := xmldom.getLength(nl);
-- 遍曆元素
for i in 0..len-1 loop
-- 構造動態 SQL 語句
strSQL := 'INSERT INTO PEOPLE VALUES (';
n := xmldom.item(nl, i);
if xmldom.getNodeName(n)='PERSON' then
nnm := xmldom.getAttributes(n); -- 讀取 PERSONID 屬性
attn := xmldom.item(nnm, 0);
strSQL := strSQL || '''' || xmldom.getNodeValue(attn) || '''';
end if;
-- 讀取 PERSON 的子節點
nl2 := xmldom.getChildNodes(n);
len2 := xmldom.getLength(nl2);
-- 處理 NAME, ADDRESS, ... 等節點
for j in 0..len2-1 loop
n := xmldom.item(nl2, j);
strSQL := strSQL || ', ''' || xmldom.getNodeValue(xmldom.getFirstChild(n)) || '''';
end loop;
-- 完成 動態 SQL 語句的構造
strSQL := strSQL || ')';
-- dbms_output.put_line(strSQL);
-- 執行插入記錄的 SQL 語句
execute immediate(strSQL); -- 執行動態 SQL
end loop;
commit; -- 提交插入
dbms_output.put_line('');
end readElements;
begin
-- 新建解析器實例
p := xmlparser.newParser;
-- 設置解析器特性
xmlparser.setValidationMode(p, FALSE);
-- xmlparser.setErrorLog(p, dir || '\' || errfile);
xmlparser.setBaseDir(p, dir);
-- 解析輸入檔
xmlparser.parse(p, dir || '\' || inpfile);
-- 獲取解析後的文檔對象
doc := xmlparser.getDocument(p);
-- 讀取文檔元素
dbms_output.put_line('讀取文檔元素並保存到表 PEOPLE 中.');
readElements(doc);
-- 釋放資源
xmldom.freeDocument(doc);
end xml2table;运行时老是提示:ora-20100: error occurred while parsing: no such file or directory.还有ora-06512等错误。請問:那位高手知道错误的原因啊?谢谢!
从XML文件中读取数据存储到XML表中:
创建Directory指向存放XML文件的路径:SQL> grant create any directory to xdb;Grant succeeded.SQL> conn xdb/xdbConnected.SQL> create directory XMLDIR as 'C:\oracle\XMLDB';Directory created. 创建存储过程,从XML文件中读取数据存储到XML表中create or replace function getClobDocument( filename in varchar2, charset in varchar2 default NULL) return CLOB deterministic is file bfile := bfilename(‘XMLDIR’,filename); charContent CLOB := ' '; targetFile bfile; lang_ctx number := DBMS_LOB.default_lang_ctx; charset_id number := 0; src_offset number := 1 ; dst_offset number := 1 ; warning number; begin if charset is not null then charset_id := NLS_CHARSET_ID(charset); end if; targetFile := file; DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly); DBMS_LOB.LOADCLOBFROMFILE(charContent, targetFile, DBMS_LOB.getLength(targetFile), src_offset, dst_offset, charset_id, lang_ctx,warning); DBMS_LOB.fileclose(targetFile); return charContent; end; /-- you can use getCLOBDocument() to generate a CLOB from a file containin-- an XML document. For example, the following statement inserts a row into the-- XMLType table Example2 created earlier: 向表中插入数据:SQL> INSERT INTO XMLTABLE 2 VALUES(XMLTYPE(getCLOBDocument('init.xml')));1 row created.SQL> INSERT INTO XMLContent 2 VALUES(1, XMLTYPE(getCLOBDocument('init.xml', 'UTF8')));1 row created.SQL> commit;Commit complete.
CREATE DIRECTORY xmldir AS 'd:\xml';
grant CREATE ANY DIRECTORY to system;CREATE TABLE xmltable (key_column VARCHAR2(10),
xml_column XMLType);-- 字符集需要根据当前数据库而定
insert into xmltable values('1',XMLType(bfilename('XMLDIR', 'emp.xml'),
nls_charset_id('UTF16')));
commit;
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
l_xmltype xmltype;
l_clob clob;
insCtx DBMS_XMLSTORE.ctxType;
l_rows NUMBER;
/*
l_clob CLOB :=
'<ROWSET>
<ROW num="1">
<ID>4</ID>
<NAME>T</NAME>
</ROW>
<ROW>
<ID>4</ID>
<NAME>黄</NAME>
</ROW>
</ROWSET>';
*/
BEGIN
qryCtx := DBMS_XMLGEN.newContext(' SELECT * FROM [email protected] md where md.produce_flag = ''Y''');
-- Set the row header to be EMPLOYEE
--DBMS_XMLGEN.setRowTag(qryCtx, 'A');
-- Get the result
l_clob := DBMS_XMLGEN.getXML(qryCtx);
--l_xmltype := DBMS_XMLGEN.getXMLTYPE(qryCtx);
insCtx := DBMS_XMLSTORE.newContext('MES_DEPARTMENT'); -- Get saved context
DBMS_XMLSTORE.clearUpdateColumnList(insCtx); -- Clear the update settings
-- Set the columns to be updated as a list of values
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'DEPT_ID');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'DEPT_NAME');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'DEPT_DESC');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'PARENT_DEPT_ID');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'PRODUCE_FLAG');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'ENABLED_FLAG');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'CREATED_BY');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'CREATED_DATE');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'LAST_UPDATED_BY');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'LAST_UPDATED_DATE');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'DELETE_FLAG'); -- Insert the doc.
l_rows := DBMS_XMLSTORE.insertXML(insCtx, l_clob);
commit;
DBMS_OUTPUT.put_line(l_rows || ' rows inserted.'); -- Close the context
DBMS_XMLSTORE.closeContext(insCtx);
DBMS_XMLGEN.closeContext(qryCtx);
END;
现在我已经在网上找到一款软件,直接就导入到oracle数据库了。各位感兴趣可以到xmlspy官方网站上下载看看。
BEGIN
xml2table(&dir,'people.XML');
end;
一定要注意输入参数dir的时候需要加引号。