Oracle XmlType Create, INSERT, UPDATE, find __oracle

[Article summary] describes the basic use of the XmlType data type of Oracle9i
includes: establishing a table with a XmlType data type
Insert (insert) Data
Query (SELECT) Data
Update ( Update) Data
Add more than 4k bytes of XML documents to XmlType fields
for beginners.

Keywords: oracle9i xmltype

Oracle supports a new data type starting at 9i---- XmlType is used to store and manage XML data and provides a number of functions for direct reading of XML documents and management nodes. Some basic uses of XmlType are described below.

1. Create table with xmltype data type
CREATE TABLE ABC (ID number,xmldoc sys.xmltype);
Declare xmltype fields with: Sys.xmltype

2. Insert with data to table with XmlType type
INSERT INTO ABC (ID,XMLDOC) value ( Abc.nextval, Sys.xmlType.createXML (' <name><a id= "1" value= "Some Values" >abc</a></name> ") );
Insert with Sys.xmlType.createXML (' some XML doc ')

3. Directly query the contents of the XmlType field
Get the value of id=1 value
Select I.xmldoc.extract ('//name/a[@id =1]/@value '). Getstringval () as Ennames, ID from ABC I

Get the value of a node
Select ID, i.xmldoc.extract ('//name/a/text () '). Getstringval () as Truename from ABC I

Get the value of the Node ID attribute

Select HD. Data_t.extract ('/name/a/@id '). Getstringval () as name from Sehr_house_data HD

4. Update the data inside the XmlType
Update ABC set Xmldoc=updatexml (xmldoc, '//name/a[@id =1]/@value ', ' some new value ') where ...
(Note: If there is no <a id= "1" > This node, you will not be able to update)

5. Add more than 4k bytes of XML documents to XmlType fields
You can accomplish this by using a temporary table:
First create a temporary table, where one of the fields is the CLOB type;
The XML doc that will be written to the XmlType field is written to this temporary CLOB-type field;
Finally insert into ABC (ID,XMLDOC) VALUES (Abc_q.nextval, Sys.xmlType.createXML (select content from temporary table where id= ...));

XML built-in features provided by Oracle 9i: Oracle 9i supports the XmlType type, which is an Oracle 9i system-defined object type. XmlType has built-in functions that provide powerful support for the creation, detection, indexing, and other functions of XML. The user can use SQL functions to generate XML documents dynamically. These functions are: Sys_xmlgen and Sys_xmlagg and Pl/sql packet Dbms_xmlgen. XML attributes Describe XmlType 1. XmlType is a data type defined by Oracle systems, and the system defines internal functions to access XML data. You can perform the following tasks: 2, create the XmlType column, use the built-in function on the XmlType column. Create Pl/sql functions and stored procedures that can be passed as parameters by using XmlType, or as a return parameter. 3. Store, index, and process XML data on the XmlType column. Dbms_xmlgen Dbms_xmlgen is a pl/sql package that transforms the results of an SQL query into a standard XML format, returns a Xmltye type or is Clob,dbms_gen implemented in the C language and placed in the kernel of the database. The Dbms_xmlgen package and the Dbms_xmlquery package are similar in function. Sys_xmlgen Xmlgen is a SQL function that is used to generate Xml,dbms_xmlgen and other package operations at the query level in SQL queries, Sys_xmlgen converts a value, object type, xmltype instance into an XML document. The type returned by Sys_xmlgen is XmlType. Sys_xmlagg Sys_xmlagg is an aggregate function that aggregates on the XmlType type. Sys_xmlagg aggregates all the input XML documents together and adds a single XML document by connecting the XML fragment to add the upper layer of the label Uritypes The Uritype type family can store and query Ur-refs,sys in the database. Uritype is an abstract data type that provides functionality to access the data that the URL points to. SYS. Httpuritype and Sys.dburitype are subtypes of uritype. SYS. The Httpuritype store is the http urls,dburitype stored intra-database reference. You can define your own sys.uritype subtypes.