Oracle xmltype是從Oracle 9i開始支持一種新的數據類型，用于存儲和管理xml數據，并提供了很多的functions，用來保存、檢索和操作xml文檔和管理節點。XMLType是系統定義的類型，所以可以使用它作為一個函數的參數或表或視圖中的列的數據類型。也可以創建表和視圖的XMLType。當你創建一個表中的一個XMLType列，你可以選擇XML數據存儲在一個CLOB列，作為二進制XML（內部存儲為CLOB），或對象的關系。

下面將介紹Oracle XMLType的一些基本使用。

1、創建一個包含XMLType類型列的表，并插入測試數據

zx@TEST>create?table?t1?(id?number,xml_data?sys.xmltype);Table?created.zx@TEST>desc?t1Name ???????Null? Type-----------------------------------------------------------------------------------------------------?--------?--------------------------------------------------------------------ID NUMBERXML_DATA SYS.XMLTYPEzx@TEST>insert?into?t1?values(1,'abc'); insert?into?t1?values(1,'abc')* ERROR?at?line?1: ORA-31011:?XML?parsing?failed ORA-19202:?Error?occurred?in?XML?processing LPX-00210:?expected?'<'?instead?of?'a' Error?at?line?1zx@TEST>insert?into?t1?values(1,'<abc>1</abc>');1?row?created.zx@TEST>col?xml_data?for?a80 zx@TEST>select?*?from?t1;ID?XML_DATA ----------?--------------------------------------------------------------------------------1?<abc>1</abc>

從上面看出，XMLType可以做為列中列的數據類型，在插入數據時必須符合XML格式才能插入，否則會報錯。

2、查看XMLType的存儲形式

從user_segments視圖中看出XMLType列是以LOB字段存儲的

zx@TEST>select?segment_name,segment_type?from?user_segments;SEGMENT_NAME ???????SEGMENT_TYPE ------------------------------?------------------------------------------------------ T1 ???????TABLE SYS_IL0000074607C00003$$???????LOBINDEX SYS_LOB0000074607C00003$$??????LOBSEGMENT

查看user_lobs是否對應xml_data列

zx@TEST>col?column_name?for?a30 zx@TEST>col?table_name?for?a30 zx@TEST>select?table_name,column_name,segment_name?from?user_lobs;TABLE_NAME ???????COLUMN_NAME ??????SEGMENT_NAME ------------------------------?------------------------------?------------------------------ T1 ???????SYS_NC00003$ ??????SYS_LOB0000074607C00003$$

從上面的查詢結果可以看到LOBSEGMENT對應的表T1中的列SYS_NC00003$，而不是XML_DATA列，而且表T1中沒有這個列，再次查詢user_tab_cols視圖

zx@TEST>col?data_type?for?a30 zx@TEST>select?TABLE_NAME,COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN,COLUMN_ID?from?user_tab_cols;TABLE_NAME ???????COLUMN_NAME ??????DATA_TYPE? ?????HIDDEN_CO COLUMN_ID ------------------------------?------------------------------?------------------------------?---------?---------- T1 ???????ID ??????NUMBER ?????NO? 1 T1 ???????XML_DATA? ??????XMLTYPE ?????NO? 2 T1 ???????SYS_NC00003$ ??????CLOB ?????YES 2

從上面的查詢中可以看出列SYS_NC00003$是表T1中的隱藏列，它與列XML_DATA列的COLUMN_ID都是2，說明它們是同一列。由此可以看出XMLType類型的數據由CLOB類型列協助保存。由下面的表定義也可以推斷出這一點：

zx@TEST>select?dbms_metadata.get_ddl('TABLE','T1',USER)?from?dual;DBMS_METADATA.GET_DDL('TABLE','T1',USER) --------------------------------------------------------------------------------CREATE?TABLE?"ZX"."T1"( "ID"?NUMBER,"XML_DATA"?"SYS"."XMLTYPE")?SEGMENT?CREATION?IMMEDIATEPCTFREE?10?PCTUSED?40?INITRANS?1?MAXTRANS?255?NOCOMPRESS?LOGGINGSTORAGE(INITIAL?65536?NEXT?1048576?MINEXTENTS?1?MAXEXTENT S?2147483645PCTINCREASE?0?FREELISTS?1?FREELIST?GROUPS?1?BUFFER_POOL?DEFAULTFLASH_CACHE?DEFAULT?CELL_FLASH_CACHE?DEFAULT)TABLESPACE?"USERS"XMLTYPE?COLUMN?"XML_DATA"?STORE?AS?BASICFILE?CLOB?(TABLESPACE?"USERS"?ENABLE?STORAGE?IN?ROW?CHUNK?8192?PCTVER SION?10NOCACHE?LOGGINGSTORAGE(INITIAL?65536?NEXT?1048576?MINEXTENTS?1?MAXEXTENTS?2147483645PCTINCREASE?0?FREELISTS?1?FREELIST?GROUPS?1?BUFFER_POOLDEFAULT?FLASH_CACHE?DEFAULT?CELL_FLASH_CACHE?DEFAUL T))

3、關于XML的一些函數

1) sys.xmltype.createxml函數

使用sys.xmltype.createxml創建XMLType類型的數據

zx@TEST>insert?into?t1?values(2,2??sys.xmltype.createxml('<?xml?version="1.0"?encoding="UTF-8"??>3??<collection?xmlns="">4????<record>5??????<leader>-----nam0-22-----^^^450-</leader>6??????<datafield?tag="200"?ind1="1"?ind2="?">7????????<subfield?code="a">抗震救災</subfield>8?????????<subfield?code="f">奧運會</subfield>9??????</datafield>10??????<datafield?tag="209"?ind1="?"?ind2="?">11????????<subfield?code="a">經濟學</subfield>12?????????<subfield?code="b">計算機</subfield>13?????????<subfield?code="c">10001</subfield>14?????????<subfield?code="d">2005-07-09</subfield>15??????</datafield>16??????<datafield?tag="610"?ind1="0"?ind2="?">17?????????<subfield?code="a">計算機</subfield>18?????????<subfield?code="a">筆記本</subfield>19??????</datafield>20????</record>21??</collection>'));1?row?created. zx@TEST>commit;Commit?complete.zx@TEST>col?xml_data?for?a80 zx@TEST>select?*?from?t1;ID?XML_DATA ----------?--------------------------------------------------------------------------------2?<?xml?version="1.0"?encoding="UTF-8"?><collection?xmlns=""><record><leader>-----nam0-22-----^^^450-</leader><datafield?tag="200"?ind1="1"?ind2="?"><subfield?code="a">抗震救災</subfield><subfield?code="f">奧運會</subfield></datafield><datafield?tag="209"?ind1="?"?ind2="?"><subfield?code="a">經濟學</subfield><subfield?code="b">計算機</subfield><subfield?code="c">10001</subfield><subfield?code="d">2005-07-09</subfield></datafield><datafield?tag="610"?ind1="0"?ind2="?"><subfield?code="a">計算機</subfield><subfield?code="a">筆記本</subfield></datafield></record></collection>

2) extractvalue函數

extractvalue函數提供對XML文件的檢索功能只能返回一個節點的一個值，如果該節點有多個值，則系統提示錯誤。

zx@TEST>col?data?for?a80 zx@TEST>select?extractvalue(i.xml_data,'/collection/record/leader')?data?from?t1?i;DATA -------------------------------------------------------------------------------- -----nam0-22-----^^^450-zx@TEST>select?extractvalue(i.xml_data,'/collection/record/datafield')?data?from?t1?i; select?extractvalue(i.xml_data,'/collection/record/datafield')?data?from?t1?i* ERROR?at?line?1: ORA-19025:?EXTRACTVALUE?returns?value?of?only?one?node

3)?extract函數

extract函數查詢XMLType的內容，它可以返回一個節點下的所有值。它返回的是XML格式的。

zx@TEST>select?extract(i.xml_data,'/collection/record/datafield/subfield')?data?from?t1?i;DATA -------------------------------------------------------------------------------- <subfield?xmlns=""?code="a">抗震救災</subfield><subfield?xmlns=""?code="f">奧運 會</subfield><subfield?xmlns=""?code="a">經濟學</subfield><subfield?xmlns=""?cod e="b">計算機</subfield><subfield?xmlns=""?code="c">10001</subfield><subfield?xml ns=""?code="d">2005-07-09</subfield><subfield?xmlns=""?code="a">計算機</subfield ><subfield?xmlns=""?code="a">筆記本</subfield>

查詢tag="610"，且code="a"所對應的值

zx@TEST>select?extract(i.xml_data,'/collection/record/datafield[@tag="610"]/subfield[@code="a"]')?data?from?t1?i;DATA -------------------------------------------------------------------------------- <subfield?xmlns=""?code="a">計算機</subfield><subfield?xmlns=""?code="a">筆記本< /subfield>

4) table和XMLSequence?

如果只想返回它值就要是用上面的兩個函數了。

zx@TEST>select?extractvalue(value(i),'/subfield')?data2??from?t1?x,3??table(xmlsequence(extract(x.xml_data,'/collection/record/datafield[@tag="610"]/subfield[@code="a"]')))?i;DATA -------------------------------------------------------------------------------- 計算機 筆記本

4) updatexml

使用updatexml更新XMLType里的內容，把tag="209"、code="a"的經濟學修改為“趙旭”

zx@TEST>update?t1?set?xml_data=2??updatexml(xml_data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]/text()','趙旭');1?row?updated.zx@TEST>select?*?from?t1;ID?XML_DATA ----------?--------------------------------------------------------------------------------2?<?xml?version="1.0"?encoding="UTF-8"?><collection?xmlns=""><record><leader>-----nam0-22-----^^^450-</leader><datafield?tag="200"?ind1="1"?ind2="?"><subfield?code="a">抗震救災</subfield><subfield?code="f">奧運會</subfield></datafield><datafield?tag="209"?ind1="?"?ind2="?"><subfield?code="a">趙旭</subfield><subfield?code="b">計算機</subfield><subfield?code="c">10001</subfield><subfield?code="d">2005-07-09</subfield></datafield><datafield?tag="610"?ind1="0"?ind2="?"><subfield?code="a">計算機</subfield><subfield?code="a">筆記本</subfield></datafield></record></collection>

參考：http://blog.csdn.net/r_youxia_dayu/article/details/6686106

http://database.51cto.com/art/200911/163928.htm

http://blog.itpub.net/17203031/viewspace-708738/

官方文檔：

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/t_xml.htm#ARPLS369

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions225.htm#SQLRF06172

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions060.htm#SQLRF00640

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions061.htm#SQLRF06173

轉載于:https://blog.51cto.com/hbxztc/1905333

總結

以上是生活随笔為你收集整理的初识Oracle的XMLType的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。