用XMLTYPE类型对XML解析: Author: mantisXF/* 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'; 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; -- 操作XMLTYPE类型的XML中的数据SQL> INSERT INTO XMLCONTENT VALUES(1,XMLTYPE(GET_CLOB_FROM_XML('company.xml','UTF8')));1 row insertedSQL> COMMIT;Commit completeSQL> SQL> SELECT EXTRACTVALUE(xmlvalue,'/company/COUNTRY/COUNTRY_CODE[1]') 2 FROM XMLCONTENT 3 WHERE XMLID = 1;EXTRACTVALUE(XMLVALUE,'/COMPAN -------------------------------------------------------------------------------- USASQL> 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> 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 -------------------------------------------------------------------------------- USASQL> SQL> SELECT existsNode(xmlvalue, '/company/COUNTRY[COUNTRY_CODE="USA"]') 2 FROM XMLCONTENT 3 WHERE XMLID = 1;EXISTSNODE(XMLVALUE,'/COMPANY/ ------------------------------ 1SQL> SQL> SELECT EXTRACT(XMLVALUE,'/company/@CID').getNumberVal() 2 FROM XMLCONTENT 3 WHERE XMLID = 1;EXTRACT(XMLVALUE,'/COMPANY/@CI ------------------------------ 1SQL> SQL> SELECT extract(xmlvalue, '/company/COUNTRY/COUNTRY_CODE[1]/text()').getStringVal() 2 FROM XMLCONTENT 3 WHERE XMLID = 1;EXTRACT(XMLVALUE,'/COMPANY/COU -------------------------------------------------------------------------------- USASQL> SQL> SELECT EXTRACT(XMLVALUE,'/company/NAME/text()').getStringVal() 2 FROM XMLCONTENT 3 WHERE XMLID = 1;EXTRACT(XMLVALUE,'/COMPANY/NAM -------------------------------------------------------------------------------- ORACLESQL> 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 updatedSQL> COMMIT;Commit completeSQL> SQL> SELECT EXTRACT(XMLVALUE,'/company/NAME/text()').getStringVal() 2 FROM XMLCONTENT 3 WHERE XMLID = 1;EXTRACT(XMLVALUE,'/COMPANY/NAM -------------------------------------------------------------------------------- mantisXFSQL>
Author: mantisXF/* 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';
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;
-- 操作XMLTYPE类型的XML中的数据SQL> INSERT INTO XMLCONTENT VALUES(1,XMLTYPE(GET_CLOB_FROM_XML('company.xml','UTF8')));1 row insertedSQL> COMMIT;Commit completeSQL> SQL> SELECT EXTRACTVALUE(xmlvalue,'/company/COUNTRY/COUNTRY_CODE[1]')
2 FROM XMLCONTENT
3 WHERE XMLID = 1;EXTRACTVALUE(XMLVALUE,'/COMPAN
--------------------------------------------------------------------------------
USASQL>
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>
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
--------------------------------------------------------------------------------
USASQL>
SQL> SELECT existsNode(xmlvalue, '/company/COUNTRY[COUNTRY_CODE="USA"]')
2 FROM XMLCONTENT
3 WHERE XMLID = 1;EXISTSNODE(XMLVALUE,'/COMPANY/
------------------------------
1SQL>
SQL> SELECT EXTRACT(XMLVALUE,'/company/@CID').getNumberVal()
2 FROM XMLCONTENT
3 WHERE XMLID = 1;EXTRACT(XMLVALUE,'/COMPANY/@CI
------------------------------
1SQL>
SQL> SELECT extract(xmlvalue, '/company/COUNTRY/COUNTRY_CODE[1]/text()').getStringVal()
2 FROM XMLCONTENT
3 WHERE XMLID = 1;EXTRACT(XMLVALUE,'/COMPANY/COU
--------------------------------------------------------------------------------
USASQL>
SQL> SELECT EXTRACT(XMLVALUE,'/company/NAME/text()').getStringVal()
2 FROM XMLCONTENT
3 WHERE XMLID = 1;EXTRACT(XMLVALUE,'/COMPANY/NAM
--------------------------------------------------------------------------------
ORACLESQL>
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 updatedSQL> COMMIT;Commit completeSQL>
SQL> SELECT EXTRACT(XMLVALUE,'/company/NAME/text()').getStringVal()
2 FROM XMLCONTENT
3 WHERE XMLID = 1;EXTRACT(XMLVALUE,'/COMPANY/NAM
--------------------------------------------------------------------------------
mantisXFSQL>