Oracle XML Datatype

by
View Xml data in oracle 21c

This article will explore the XML data type in Oracle 21c, including its syntax, advantages, and example usage.

Introduction to Oracle XML Datatype

An extremely popular data format for storing and transferring structured data is XML (Extensible Markup Language). Because it offers native support for working with XML data, Oracle 21c makes it simple to save, retrieve, and manipulate XML data.

With the help of the XML datatype, XML data may be stored more efficiently and effectively and processed more quickly and precisely.

Also, check: Oracle Binary_float Datatype

Advantages of XML Datatype

  • Flexible data modeling: Structured data may be easily exchanged and stored in various formats due to XML’s support for flexible data modeling.
  • Native XML support: In Oracle 21c, the XML data type has native support, making it simple to work with XML data in your database without additional libraries or tools.
  • Improved data integrity: The XML data type in Oracle 21c ensures data integrity by validating incoming XML data against a defined XML schema or DTD (Document Type Definition).
  • Efficient storage and retrieval: Oracle 21c provides efficient storage and retrieval of XML data, making it easy to manage large volumes of XML data in your database.

Syntax of XML Datatype in Oracle 21c

The syntax for creating a column with the XML datatype in Oracle 21c is as follows:

CREATE TABLE table_name (
  xml_column_name XML
);

This generates a table with a single XML datatype column named xml_column_name. You can use any name you choose in place of the table name and xml column name.

Using the XML(size) syntax, you may also set a maximum size for the XML data. For instance, the following syntax would be used to define that the XML data’s maximum size is 10 MB.

CREATE TABLE table_name (
  xml_column_name XML(10M)
);

Read: Nchar Datatype in Oracle Database

Example usage of XML datatype in Oracle 21c

Here’s an example of how to use the XML data type in Oracle 21c:

CREATE TABLE employees (
   emp_id NUMBER,
   xml_data XMLType
);

Now we will insert the values in the following given table

-- Insert an XML document into the table
INSERT INTO employees (emp_id, xml_data) VALUES (
   56,
   XMLType('<person><name>John Smith</name><age>35</age></person>')
);

-- Retrieve the XML document from the table
SELECT * from employees;

This code creates a table called ’employees’ with two columns: emp_id and xml_data. The xml_data column is of data type XMLType. The INSERT statement inserts an XML document into the xml_data column, and the SELECT statement retrieves the XML document from the table.

Using the xml datatype in Oracle 21c
Using the xml datatype in Oracle 21c

Once you will double click on the xmltype. It will display the view of XML data.

View Xml data in oracle 21c
View Xml data in oracle 21c

Storage and performance characteristics of the XML datatype

The storage and performance characteristics of the XML datatype in Oracle 21c can vary depending on the size and complexity of the XML documents being stored. Consider the following important factors:

  • Storage: In Oracle 21c, XML data is often saved in the compressed binary format known as binary XML.
  • Performance: The XML datatype in Oracle 21c supports a wide range of XML-specific functions and operators, such as XMLType, XMLQuery, and XSLT. These functions can be used to efficiently parse, manipulate, and transform XML data.

Also, check: Oracle Create Index

Conclusion:

So, In this Oracle tutorial, we understood how to define and use the XML Datatype in Oracle Database. And we have also covered a few sample examples related to it.

Also, take a look at some more Oracle tutorials.