The first knowledge of Oracle's XmlType

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