Loading XML Documents

電子病歷是一份XML格式的文件。傳統上都是檔案的型式儲存於檔案伺服器上管理，然後在關聯式資料庫表格，用"pointer"指向儲存的檔案位置。在安全與查詢(指針對文件內容)都有諸多限制。
Oracle 資料庫提供原生性的XML欄位格式，提供以XQuery或是SQL view的方式提供查詢XML文件內容。下列的指令, 是如何將XML文件放入Oracle資料庫的步驟

1. Create a XML table. (如何建立XML表格)
建立一個XML資料表格，我的意思是一 XML Table只有一個欄位, XMLType欄位。
This is easy. As a normal table creation but define a column which its data type is XMLType.

  CREATE TABLE "CGMH"."XML_DOCS"
   ( "CDA" "SYS"."XMLTYPE"
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 XMLTYPE COLUMN "CDA" STORE AS BASICFILE BINARY XML (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  CACHE READS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA ;

2. Load XML documents into XML table(將 XML文件載入 XML Table)
透過"目錄路徑"的物件，指定上載XML文件所在的路徑。在這裏我們首先建立"XMLDIR"的目錄路徑物件；然後再使用XMLType 函式載入文件。

Things to be noticed is the encoding. Or I will lost the characters in XML documents.

Login Oracle Database as system to create Directory(or check if any Directory are available to use)
SQL> create directory XMLDIR as '/u01/opt/XML';

SQL> grant read, write on directory XMLDIR to cgmh;
Login as a Database user to load XML documents

SQL> connect cgmh/cgmh

Connected.

SQL> select * from all_directories;

OWNER                          DIRECTORY_NAME

------------------------------ ------------------------------

DIRECTORY_PATH

--------------------------------------------------------------------------------

SYS                            XMLDIR

/u01/opt/XML

SQL> insert into XML_DOCS values

       2 (XMLType(bfilename('XMLDIR', 'CDABig5Sample.xml'), nls_charset_id('ZHT16MSWIN950')));

1 row created.

SQL> insert into XML_DOCS values

       2 (XMLType(bfilename('XMLDIR', 'CDASample.xml'), nls_charset_id('AL32UTF8')));

1 row created.

SQL> commit;

Commit complete.