当前位置:文档之家› 用XMLTYPE类型 解析并操作XML数据

用XMLTYPE类型 解析并操作XML数据

Oracle 中 用XMLTYPE类型 解析并操作XML数据 收藏
今天在论坛中有guys发问关于XML入oracle数据库并进行相关数据操作的问题,所以趁周末总结一下,嘿嘿。毕竟以后XML是数据库发展的趋势。

用XMLTYPE类型对XML解析:

/* XML file detail


0001
ORACLE

USA
CHINA
UK



*/

CREATE DIRECTORY XMLDIR AS 'C:\oracle\XMLDIRTY';

--如果没有创建directory的权限,以有DBA权限的帐户登录并执行下面的语句赋权:

-- GRANT CREATE ANY DIRECTORY TO SCOTT;
CREATE TABLE CLOB_TABLE (CID NUMBER,CONTENT CLOB);

CREATE TABLE XMLCONTENT (XMLID NUMBER PRIMARY KEY, XMLVALUE XMLTYPE);

INSERT INTO CLOB_TABLE(CID) VALUES(1); --CONTENT列没有定位器,并且没有数据
INSERT INTO CLOB_TABLE VALUES(2,'THE CONENT WORK FOR CID 2'); --CONTENT列有定位器,并且有数据
INSERT INTO CLOB_TABLE VALUES(3,EMPTY_CLOB());--CONTENT含有定位器,但是没有数据

COMMIT;

-- 用下面的函数把XML文件转换成CLOB对象,然后使用XMLTYPE类型插入并保存在表XMLCONTENT中.

/*
Author: mantisXF
Date: Sep 6, 2008
*/

CREATE OR REPLACE FUNCTION GET_CLOB_FROM_XML(FILENAME IN VARCHAR2,
CHARSET IN VARCHAR2 DEFAULT NULL
)RETURN CLOB DETERMINISTIC
IS
-- DECLARE VARIABLE E.G: CLOB,FILENAME
V_XML_FILE BFILE := BFILENAME('XMLDIRTY',FILENAME);
-- WORK FOR CONVENTING XML FILE TO CLOB OBJECT
V_CLOBCONTENT CLOB;
V_LANG_CTX NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
V_CHARSETID NUMBER := 0;
V_SRC_OFFSET NUMBER := 1;
V_DST_OFFSET NUMBER := 1;
V_WARNING NUMBER;

V_STEP VARCHAR2(100) := '';

BEGIN
V_STEP := 'DECIDE WHETHER CHARSET IS REQUIRED';
IF CHARSET IS NOT NULL THEN
V_CHARSETID := NLS_CHARSET_ID(CHARSET);
END IF;

V_STEP := 'MAKE LOB LOCATOR CAN HAVE THE PLACE BY SELECTING RESULT FROM CLOB_TABLE';
SELECT NVL(CONTENT,EMPTY_CLOB()) INTO V_CLOBCONTENT FROM CLOB_TABLE WHERE CID = 1 FOR UPDATE;

V_STEP := 'OPENING XML FILE';
DBMS_LOB.FILEOPEN(V_XML_FILE,DBMS_LOB.FILE_READONLY);

V_STEP := 'CONVERTING XML FILE TO CLOB OBJECT';
DBMS_LOB.LOADCLOBFROMFILE(V_CLOBCONTENT,V_XML_FILE,DBMS_LOB.GETLENGTH(V_XML_FILE),V_SRC_OFFSET,V_DST_OFFSET,V_CHARSETID,V_LANG_CTX,V_WARNING);

V_STEP := 'CLOSING XML FILE OBJECT';
DBMS_LOB.FILECLOSE(V_XML_FILE);

RETURN V_CLOBCONTENT;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error at: '|| V_STEP);
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' : ' || SQLERRM);

END;



/*

在编写上面的FUNCTION时可能会出现相关的问题(至少俺碰到了:():

1>:



SQL> INSERT INTO XMLCONTENT VALUES(1,XMLT

YPE(GET_CLOB_FROM_XML('company.xml','UTF8')));

Error at: CONVERTING XML FILE TO CLOB OBJECT
-6502 : ORA-06502: PL/SQL: 数字或值错误 : invalid LOB locator specified: ORA-22275



官方的解释是:

Cause: There are several causes:
(1)The LOB locator was never initialized
(2)The locator is for a BFILE and the routine expects a BLOB/CLOB/NCLOB locator
(3)The locator is for a BLOB/CLOB/NCLOB and the routine expects a BFILE locator
(4)Trying to update the LOB in a trigger body -- LOBs in trigger bodies are read-only
(5)The locator is for a BFILE/BLOB and the routine expects a CLOB/NCLOB locator
(6)The locator is for a CLOB/NCLOB and the routine expects a BFILE/BLOB locator
Action: For (1), initialize the LOB locator by selecting into the locator variable or by setting the LOB locator to empty. For (2),(3), (5), and (6),pass the correct type of locator into the routine. For (4), remove the trigger body code that updates the LOB value.


最后发现是:LOB在对其进行操作之前一定要进行初始化,也就是说,为其分配一个定位符。利用empty_lob()就可以给LOB指定一个特定的标记。这个时候还不能读取其中的内容,因为它没有定位符,只是一个空指针而已。如果读取的话,就会抛出异常。



所以俺的解决方案是:

SELECT NVL(CONTENT,EMPTY_CLOB()) INTO V_CLOBCONTENT FROM CLOB_TABLE WHERE CID = 1 FOR UPDATE;


这样的话就已经为该CLOB分配了一个定位符并赋值。



2>:

SQL> INSERT INTO XMLCONTENT VALUES(1,XMLTYPE(GET_CLOB_FROM_XML('company.xml','UTF8')));

Error at: OPENING XML FILE
-22290 : ORA-22290: 操作将超过打开的文件或 LOB 的最大数目



这个问题是因为打开了很多XML文件并且超过最大数目。相应的解决方案是在FUNCTION最后把文件关闭。

*/
-- 操作XMLTYPE类型的XML中的数据

SQL> INSERT INTO XMLCONTENT VALUES(1,XMLTYPE(GET_CLOB_FROM_XML('company.xml','UTF8')));

1 row inserted

SQL> COMMIT;

Commit complete

SQL> set long 100000
SQL> SELECT *
2 FROM XMLCONTENT;

XMLID XMLVALUE
---------- --------------------------------------------------------------------------------
1


0001
ORACLE

USA
CHINA
UK





SQL> SELECT EXTRACTVALUE(xmlvalue,'/company/COUNTRY/COUNTRY_CODE[1]')
2 FROM XMLCONTENT
3 WHERE XMLID = 1;

EXTRACTVALUE(XMLVALUE,'/COMPAN
--------------------------------------------------------------------------------
USA
SQL> SELECT EXTRACT(xmlvalue,'/company/COUNTRY')
2 FROM XMLCONTENT
3 WHERE XMLID = 1;


EXTRACT(XMLVALUE,'/COMPANY/COU
--------------------------------------------------------------------------------

USA
CHINA
UK


SQL> SELECT EXTRACTVALUE(xmlvalue,'/company/COUNTRY/text()')
2 FROM XMLCONTENT
3 WHERE XMLID = 1;

EXTRACTVALUE(XMLVALUE,'/COMPAN
--------------------------------------------------------------------------------

SQL>
SQL> SELECT extractValue(xmlvalue, '/company/COUNTRY/COUNTRY_CODE[1]/text()')
2 FROM XMLCONTENT
3 WHERE XMLID = 1
4 AND existsNode(xmlvalue, '/company/COUNTRY[COUNTRY_CODE="USA"]')
5 = 1;

EXTRACTVALUE(XMLVALUE,'/COMPAN
--------------------------------------------------------------------------------
USA
SQL> SELECT existsNode(xmlvalue, '/company/COUNTRY[COUNTRY_CODE="USA"]')
2 FROM XMLCONTENT
3 WHERE XMLID = 1;

EXISTSNODE(XMLVALUE,'/COMPANY/
------------------------------
1


SQL> SELECT EXTRACT(XMLVALUE,'/company/@CID').getNumberVal()
2 FROM XMLCONTENT
3 WHERE XMLID = 1;

EXTRACT(XMLVALUE,'/COMPANY/@CI
------------------------------
1


SQL> SELECT extract(xmlvalue, '/company/COUNTRY/COUNTRY_CODE[1]/text()').getStringVal()
2 FROM XMLCONTENT
3 WHERE XMLID = 1;

EXTRACT(XMLVALUE,'/COMPANY/COU
--------------------------------------------------------------------------------
USA


SQL> SELECT EXTRACT(XMLVALUE,'/company/NAME/text()').getStringVal()
2 FROM XMLCONTENT
3 WHERE XMLID = 1;

EXTRACT(XMLVALUE,'/COMPANY/NAM
--------------------------------------------------------------------------------
ORACLE


SQL> UPDATE XMLCONTENT
2 SET xmlvalue = updateXML(xmlvalue,'/company/NAME/text()','mantisXF')
3 WHERE XMLID = 1
4 AND EXISTSNODE(xmlvalue,'/company[NAME="ORACLE"]') = 1;

1 row updated

SQL> COMMIT;

Commit complete


SQL> SELECT EXTRACT(XMLVALUE,'/company/NAME/text()').getStringVal()
2 FROM XMLCONTENT
3 WHERE XMLID = 1;

EXTRACT(XMLVALUE,'/COMPANY/NAM
--------------------------------------------------------------------------------
mantisXF

SQL>



其实还有更多高级的方法去操作XML数据,详细见Oracle官方文档:

https://www.doczj.com/doc/ed7403683.html,/docs/cd/B19306_01/appdev.102/b14259/xdb04cre.htm#sthref465

还有ORACLE高手的文章:

https://www.doczj.com/doc/ed7403683.html,/Doc/oracle_xml_db.htm



以后会陆续更新相关的方法,欢迎讨论和指出错误。


相关主题
相关文档 最新文档