Oracle XmlType starts with Oracle 9i to support a new data type that stores and manages XML data and provides a number of functions for saving, retrieving, and manipulating XML documents and managing nodes. XmlType is a system-defined type, so it can be used as a parameter to a function or as a data type for a column in a table or view. You can also create xmltype for tables and views. When you create a xmltype column in a table, you can choose to store the XML data in a CLOB column, as binary XML (internally stored as CLOB), or as an object relationship.
Some basic uses of Oracle XmlType are described below.
1. Create a table containing the XmlType type column and insert the test data
[email protected]>create table t1 (Id number,xml_data sys.xmltype); Table created. [Email protected]>desc t1 name null? type ---------------------------------------------------------------------------------------------------- - -------- -------------------------------------------------------------------- IDNUMBER Xml_datasys. Xmltype [email protected]>insert into t1 values (1, ' abc '); insert into t1 values (1, ' abc ') *error at line 1:ora-31011: xml parsing failedora-19202: error occurred in XML processingLPX-00210: expected ' < ' instead of ' a ' error at Line 1[email protected]>insert into t1 values (1, ' <abc>1</abc> '); 1 row creaTed. [Email protected]>col xml_data for a80[email protected]>select * from t1;id xml_data---------- --------------------------------------------------------------------- ----------- 1 <abc>1</abc>
As seen from the above, XmlType can be used as the data type of columns in columns, and must conform to XML format when inserting data, otherwise it will be an error.
2. View XmlType storage format
See from the user_segments view that the XmlType column is stored as a LOB field
[Email protected]>select segment_name,segment_type from user_segments; Segment_name segment_type------------------------------------------------------------------------------------T1 tablesys_il0000074607c00003$$ lobindexsys_lob0000074607c00003$$ lobsegment
See if User_lobs corresponds to xml_data column
[Email Protected]>col column_name for A30[email protected]>col table_name for a30[email protected]>select table _name,column_name,segment_name from User_lobs; TABLE_NAME column_name segment_name-------------------------------------------------------------------------- ----------------T1 sys_nc00003$ sys_lob0000074607c00003$$
From the results of the above query, you can see the column sys_nc00003$ in table T1, not the Xml_data column, and the T1 view lobsegment the table User_tab_cols.
[email protected]>col data_type for a30[email protected]>select table_name,column_name,data_type,hidden_column,column_id from user_ Tab_cols; Table_name column_name data_type hidden_cocolumn_id------------------------------ -------------------- ---------- ------------------------------ --------- ----------t1 ID NUMBER NO 1T1 XML_DATA XMLTYPE NO 2T1 SYS_NC00003$ clob yes2
From the query above, you can see that column sys_nc00003$ is a hidden column in table T1, and that the column_id of column xml_data columns is 2, indicating that they are the same column. From this you can see that the XmlType type of data is saved by the CLOB type column. This can also be inferred from the following table definition:
[Email protected]>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 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 "Xml_data" STORE AS BASICFILE CLOB ( TABLESPACE " USERS " enable storage&nbsP;in row chunk 8192 pctversion 10 nocache 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))
3. Some functions about XML
1) sys.xmltype.createxml function
Using Sys.xmltype.createxml to create xmltype types of data
[Email protected]>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= " ind1=" 1 " ind2=" "> 7 <subfield code= "A" > Earthquake relief </subfield> 8 <subfield code= "F" > Olympic Games </subfield> 9 </datafield> 10 < datafield tag= "209" ind1= " " ind2= " " > 11 <subfield code= "A" > Economics </subfield> 12 <subfield code= "B" > Computer </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" > Computer </subfield> 18 <subfield code= "A" > Notebooks </subfield> 19 </datafield> 20 </record> 21 </collection> ')); 1 row created. [email protected]>commit; Commit complete. [Email protected]>col xml_data for a80[email protected]>select * from t1; id xml_data---------- ------------------------------------ -------------------------------------------- 2 <? Xml version= "1.0" encoding= "UTF-8"?> <collection xmlns= "" > <record> <leader>-----nam0-22-----^^ ^450-</leader> <datafield tag= " ind1=" 1 " ind2=" "> <subfield code= "A" > Anti-earthquake rescueDisaster </subfield> <subfield code= "F" > Olympic Games </subfield> </datafield> <datafield tag= "209" ind1= " " ind2= " " > < Subfield code= "A" > Economics </subfield> <subfield code= "B" > Computer </subfield> <subfield code= "C" >10001</subfield> &lT;subfield code= "D" >2005-07-09</subfield> </datafield> <datafield tag= "610" ind1= "0" ind2= " " > <subfield code= "A" > Computer </subfield> <subfield code= "A" > Notebooks </subfield> </datafield> </record> </collection>
2) Extractvalue function
The Extractvalue function provides the ability to retrieve an XML file to return only one value of one node, and if the node has multiple values, the system prompts for an error.
[Email protected]>col data for a80[email protected]>select extractvalue ( I.xml_data, '/collection/record/leader ') data from t1 i;d ATA-------------------------------------------------------------------------------------nam0-22-----^^ ^450-[ Email protected]>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 function
The Extract function queries the contents of the XmlType, which can return all values under a single node. It returns the XML format.
[Email Protected]>select extract (i.xml_data, '/collection/record/datafield/subfield ') data from T1 I;D ATA--------------------------------------------------------------------------------<subfield xmlns= "" Code= "a "> Earthquake relief </subfield><subfield xmlns=" "code=" F "> Olympic </subfield><subfield xmlns=" "code=" a "> Economics </subfield><subfield xmlns= "" code= "B" > Computer </subfield><subfield xmlns= "" code= "C" >10001 </subfield><subfield xmlns= "" code= "D" >2005-07-09</subfield><subfield xmlns= "" code= "a" > Computer </subfield><subfield xmlns= "" code= "a" > Notebooks </subfield>
Query tag= "610" and the value corresponding to Code= "a"
[Email Protected]>select extract (i.xml_data, '/collection/record/datafield[@tag = "610"]/subfield[@code = "a"]) Data from T1 i;data--------------------------------------------------------------------------------<subfield xmlns= "" Code= "a" > Computer </subfield><subfield xmlns= "" code= "a" > Notebooks </subfield>
4) Table and Xmlsequence
If you want to return it only, use the above two functions.
[Email Protected]>select extractvalue (value (i), '/subfield ') data 2 from T1 x, 3 table (Xmlsequence (Extract (X.xml_dat A, '/collection/record/datafield[@tag = "610"]/subfield[@code = "A"])) i;d ATA--------------------------------------------------------------------------------Computer Notebooks
4) Updatexml
Use Updatexml to update the contents of XmlType, tag= "209", code= "a" to the economics of "Zhao Xu"
[Email protected]>update t1 set xml_data= 2 updatexml (Xml_data, '/ collection/record/datafield[@tag = "209"]/subfield[@code = "a"]/text () ', ' Zhao Xu '); 1 row updated. [Email protected]>select * from t1; id xml_data---------- -------------------------------------------------------------------------------- 2 <?xml version= "1.0" encoding= "UTF-8" ? > <collection xmlns= "" > <record> <leader>-----nam0-22-----^^ ^450-</ leader> < Datafield tag= " in"d1= "1" ind2= " " > <subfield code= "A" > Earthquake relief </subfield> <subfield code= "F" > Olympic </ subfield> </ datafield> < datafield tag= "209" ind1= " " ind2= " " > <subfield code= "A" > Zhao Xu </subfield> < Subfield code= "B" > Computer </subfield> <subfield code= "C" >10001</subfield> <subfield code= "D" >2005-07-09</subfield > </datafield > <datafield tag= "610" ind1= "0" ind2= " " > <subfield code= "A" > Computer </subfield> <subfield Code= "A" > Notebooks </subfield> </datafield> </record> &nBsp; </collection>
Reference: 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/
Official documents:
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
This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1905333
The first knowledge of Oracle's XmlType