Updated March 4, 2023
Definition of Oracle XMLType Function
Oracle has different data types and XMLType is a new data type, it is used to handle the XML data in the oracle database. Basically, XMLTYpe is used in PL/SQL stored procedures for different purposes such as parameters, return values, and variables. We can represent XML documents as an instance of XMLType in SQL with help of XMLType data type. XMLType data type has a built-in member function that works on XML content. For example, we can use the XMLType data type function to extract, create and index XML data stored in the database. XMLType also provides the different types of functionality by using the Application Program Interfaces that means API.
Syntax:
create table specified table (schema) name of XMLType;
Explanation
In the above syntax, we use create table statement, here specified table name means actual table name that we need to create, and XMLTYPE is used as a column name. In the above syntax, we use the XMLtype API to create a table that is schema and columns. The createXML() is a static function of XMLType API and it is used to create XMLType instances for insert operation.
How XMLType function works in Oracle?
Now let’s see how the XMLType function works in Oracle as follows.
Basically, XMLType is a system-defined data type that is used for handling the XML data and it also has a predefined member function.
By using XMLType API we can perform different operations as follows.
- Creating XMLType Columns
- Adding XMLType Columns
- Dropping XMLType Columns
Now see how we can create XMLType Instance as follows.
We can use CMLType constructor to create the XMLType instance before insert operation into the column or table as well as we can also use different types of other functions that return the XMLType. After creating an instance of XMLType, we need to select or extract that created instance for a particular XMLType. So we can choose XMLType instance from the column, at that same time XMLType data type offers to select member function such as extract() and existsNode(). The extract is used to check whether a particular node exists or not.
Oracle Text Index
We can also define the Text Index on the XMLType column as well as we can use contains, haspath, inpath, and other text operators. All text operator and index functions of Oracle operate on LOB columns.
Define XPath Index, CTXXPATH
The CTXXPATH and Oracle Text Index type is a new release in oracle. This is helpful to implement indexing and optimizes the evaluation of existsNode() function.
We can specify Storage Characteristics on XMLType Columns. In which that XMLtype column is used to store as a CLOB column. In XMLTYpe data type we can change the storage option by using the XML data.
XMLType Operation
XMLTypes provides the different operations as follows.
1. Select XML data
We can select XMLType columns by using PL/SQL, SQL, or Java. We can also use query directly by using extract () and existNode () functions.
2. Select XML document by using XPath expression
Basically, XPath is used to navigate the XML document and it works as a tree structure. Now let’s see some common XPath Constructs as follows.
/ It is used to denote the root of a tree in the XPath expression.
// this is used to define all descendants of the current node.
Star (*) It is used to match child nodes.
[] it is used to predicate the expression. XPath is supported by a list of binary operators such as OR, AND, or NOT.
Functions XPath is also supported inbuilt functions such as substring (), round (), and not ().
Examples
Now let’s see a different example of Oracle XMLType function as follows.
CREATE TABLE sampleXML OF XMLTYPE;
Explanation
In the above example, we use the create table command to create a new table name as sampleXML, here OF is keyword and XMLTYPE is a column of sampleXML table as shown in the above statement. The end output of the above query we illustrate by using the following snapshot.
Now insert some records by using XML as follows.
INSERT INTO sampleXML VALUES
(xmltype('<?xml version="1.0"?>
<Home>
<HomeID>1</HomeID>
<HomeName>Jalsa, ABC</ HomeName >
<HomeNO>20</HomeNO>
<HomeArea>35000</HomeArea>
<Street>M.G Road</Street>
<HomeWaterAccess>true</HomeWaterAccess>
<RailAccessLocation>Near</RailAccessLocation>
<Parking>Yes</Parking>
<Clearance>20</Clearance>
</Home>'));
Explanation
In the above statement, we use to insert into a statement to insert records into the sampleXML table by using an XML schema as shown in the above statement. In this example, we implement an XMLType table. The end output of the above query we illustrate by using the following snapshot.
Now select XMLType columns using getCLOBVal() as follows.
select x.object_value.getCLOBVal() from sampleXML x;
Explanation
In the above example we use the getCLOBVal() function to select an XMLType column from sampleXML table. When we execute the above query then the final output we illustrate by using the following snapshot.
Let’s see another example of XMLType as follows.
CREATE TABLE demo_xml(
id number,
Doc XMLTYPE);
Explanation
In the above example, we use the create table statement to create a new table name as demo_xml with two attributes by using XMLTYPE as shown in the above statement. When we execute the above query then the final output we illustrate by using the following snapshot.
Now we can add one more column into the demo_xml table as follows.
alter table demo_xml add (doctype XMLTYPE);
Explanation
In the above example, we use the alter command to add a new column name as doctype as shown in the above statement. When we execute the above query then the final output we illustrate by using the following snapshot.
Now we can perform the drop XMLType column command as follows.
alter table demo_xml drop (doctype);
Explanation
In the above example, we use the alter table command to drop the newly added column but here we can’t perform the drop command because of privileges., so here it shows an error message. When we execute the above query then the final output we illustrate by using the following snapshot.
Conclusion
We hope from this article you have understood the Oracle XMLType function. From this article, we have learned the basic syntax of the XMLType function and we also see different examples of the XMLType function. From this article, we learned how and when we use the Oracle XMLType function.
Recommended Articles
This is a guide to Oracle XMLType. Here we discuss the definition, syntax, parameters, and How the XMLType function works in Oracle? with code implementation. You may also have a look at the following articles to learn more –
