Oracle XML DB (I) using Oracle10g

/*
　IBM Database Magazine DB2用「A Surprising Ace in the Hole」來形容XML的performance。
　到底真這麼強嗎? 我不知道。因為我並沒有 DB2 9 可以做測試。
　而Oracle 10g我是有的。Oracle在External Table的效能向來都是很好的，
　所以拿Oracle來操作一次。
　※　IBM的小氣程度才是「A Surprising Ace in the Hole」咧!
*/


-- 前置處理
-- Create Directory

SQL>CREATE OR REPLACE DIRECTORY XMLDIR
　2　AS C:\oracle\product\10.2.0\ora_xml';
SQL>GRANT READ,WRITE ON DIRECTORY XMLDIR TO orion;

--AL32UTF8的NLS_CHARSET_ID是873

SQL>select nls_charset_id('AL32UTF8') from dual;

873

--按照Oracle XML DB (Database Developer's Guide)說的
-- 也建一個GetDocument Function好將指定的文件開啟成為clob讀出來
-- 不過這裡用的是loadclobfromfile


create or replace function getDocument(filename varchar2) return clob
　　　authid current_user is
xbfile bfile;
xclob clob:=' ';
lang_ctx number := DBMS_LOB.default_lang_ctx;
src_offset number := 1;
dst_offset number := 1;
warning number;
begin
　　　　xbfile := bfilename('XMLDIR',filename);
　　　　dbms_lob.fileopen(xbfile,dbms_lob.file_readonly);
　　　　dbms_lob.loadclobfromfile(xclob,xbfile,dbms_lob.getlength(xbfile),src_offset,dst_offset,873,lang_ctx,warning);
　　　　dbms_lob.fileclose(xbfile);
　　　　return xclob;
end;


--準備一個area.xsd file，內容如下:
--※文件裡已將table名稱設定為AREA


< xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb" xdb:storeVarrayAsTable="true" >
　< xs:element name="ROWSET" type="ROWSET_TYPE" xdb:defaultTable="AREA" / >
　　< xs:complexType name="ROWSET_TYPE" xdb:SQLType="AREA_OT" xdb:maintainDOM="false" >
　　　< xs:sequence >
　　　　< xs:element name="THISROW" type="ROW_TYPE" xdb:SQLCollType="AREA_ROW_NTT" minOccurs="0" maxOccurs="unbounded" xdb:maintainOrder="false"/ >
　　　< /xs:sequence >
　　　< /xs:complexType >
　　　< xs:complexType name="ROW_TYPE" xdb:SQLType="AREA_ROW_OT" xdb:maintainDOM="false" >
　　　　< xs:sequence >
　　　　　< xs:element name="AREAID" type="xs:int" xdb:SQLType="NUMBER"/ >
　　　　　< xs:element name="AREA_ENAME" type="xs:string"/ >
　　　　　< xs:element name="AREA_CNAME" type="xs:string"/ >
　　　　< /xs:sequence >
　　　< /xs:complexType >
< /xs:schema >

--準備一個area.xml file，內容如下:
/*
　　※ area.xml除了encoding要設為UTF-8之外，儲存時須將格式設為UTF-8，
　　　否則 中文字 在browser以及使用loadclobfromfile時會出現亂碼
　　※ http://localhost:8080/public/orion/xsd/area.xsd是
　　　area.xsd將註冊及存放的地方，大小寫是有區分的
*/

< ?xml version="1.0" encoding="UTF-8" ? >
< ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080/public/orion/xsd/area.xsd" >
< THISROW >< AREAID >1< /AREAID>< AREA_ENAME >Africa< /AREA_ENAME >< AREA_CNAME >非洲< /AREA_CNAME >< /THISROW >
< THISROW >< AREAID >2< /AREAID >< AREA_ENAME >America< /AREA_ENAME >< AREA_CNAME >美洲< /AREA_CNAME >< /THISROW >
< THISROW >< AREAID >3< /AREAID >< AREA_ENAME >Asia< /AREA_ENAME >< AREA_CNAME >亞洲< /AREA_CNAME >< /THISROW >
< THISROW >< AREAID >4< /AREAID >< AREA_ENAME >Europe< /AREA_ENAME >< AREA_CNAME >歐洲< /AREA_CNAME >< /THISROW >
< THISROW >< AREAID >5< /AREAID >< AREA_ENAME >Middle East< /AREA_ENAME >< AREA_CNAME >中東< /AREA_CNAME >< /THISROW >
< THISROW >< AREAID >6< /AREAID >< AREA_ENAME >Oceania< /AREA_ENAME >< AREA_CNAME >大洋洲< /AREA_CNAME >< /THISROW >
< /ROWSET >


--試用看看

SQL> select XMLTYPE(getDocument('area.xml')) from dual;

XMLTYPE(GETDOCUMENT('AREA1.XML'))
-----------------------------------------------------------------------
< ?xml version="1.0" encoding="BIG5"? >
< ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://
localhost:8080/public/orion/xsd/area.xsd" >
< THISROW >
　　< AREAID >1< /AREAID >
　　< AREA_ENAME >Africa< /AREA_ENAME >
　　< AREA_CNAME >非洲< /AREA_CNAME >
< /THISROW >
< THISROW >
　　< AREAID >2< /AREAID >
　　< AREA_ENAME >America< /AREA_ENAME >
　　< AREA_CNAME >美洲< /AREA_CNAME >
< /THISROW >
< THISROW >
　　< AREAID >3< /AREAID >
　　< AREA_ENAME >Asia< /AREA_ENAME >
　　< AREA_CNAME >亞洲< /AREA_CNAME >
< /THISROW >
< THISROW >
　　< AREAID >4< /AREAID >
　　< AREA_ENAME >Europe< /AREA_ENAME >
　　< AREA_CNAME >歐洲< /AREA_CNAME >
< /THISROW >
< THISROW >
　　< AREAID >5< /AREAID >
　　< AREA_ENAME >Middle East< /AREA_ENAME >
　　< AREA_CNAME >中東< /AREA_CNAME >
< /THISROW >
< THISROW >
　　< AREAID >6< /AREAID >
　　< AREA_ENAME >Oceania< /AREA_ENAME >
　　< AREA_CNAME >大洋洲< /AREA_CNAME >
< /THISROW >
< /ROWSET >


--　待續 Oracle XML DB(II)