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

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

用XMLTYPE类型对XML解析：

/*   XML file detail

<company CID="1">
      <ID>0001</ID>
      <NAME>ORACLE</NAME>
      <COUNTRY CTID="1">
        <COUNTRY_CODE>USA</COUNTRY_CODE>
        <COUNTRY_CODE>CHINA</COUNTRY_CODE>
        <COUNTRY_CODE>UK</COUNTRY_CODE>
      </COUNTRY>
</company>

*/

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,XMLTYPE(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          <ROWSET>
            <ROW>
             <company>
                 <ID>0001</ID>
                 <NAME>ORACLE </NAME>
                 <COUNTRY>
                   <COUNTRY_CODE>USA</COUNTRY_CODE>
                   <COUNTRY_CODE>CHINA</COUNTRY_CODE>
                   <COUNTRY_CODE>UK</COUNTRY_CODE>
                 </COUNTRY>
             </company>
            </ROW>
           </ROWSET>

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
--------------------------------------------------------------------------------
<COUNTRY CTID="1">
  <COUNTRY_CODE>USA</COUNTRY_CODE>
  <COUNTRY_CODE>CHINA</COUNTRY_CODE>
  <COUNTRY_CODE>UK</COUNTRY_CODE>
</COUNTRY>

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官方文档：

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb04cre.htm#sthref465

还有ORACLE高手的文章：

http://www.hellodba.com/Doc/oracle_xml_db.htm

 

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

 

Hope it will helpful for us & Good luck to us!