EDUCBA

Home Data Science Data Science Tutorials Oracle Tutorial Oracle XML

Oracle XML

Sohel Sayyad
Article bySohel Sayyad
Priya Pedamkar
Reviewed byRavi Rathore

Updated May 15, 2023

Oracle XML

 

 

Definition on Oracle XML

Oracle XML means it is a database name related to high-performance XML storage and data retrieval. Basically, oracle XAML is supported for both SQL and XML data models in an interchangeable manner. The XML schema is a standard access method for navigating and executing queries with XML. With the help of XML, we can perform different operations such as insert, delete and update as well as we are also able to perform the different operations on SQL data. The oracle XML supports the different data types such as abstract SQL data type, XMLType for XML data. The main feature of oracle databases is reliability, availability, scalability, and security. When we talk about the architecture of oracle XML then it uses the repository to store any type of data or document including the XML document associated with XML schema.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

create table specified table 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.

How XML work in Oracle?

It has mainly two features as follows.

  1. XMLType tables and views
  2. Oracle XML DB Repository.

XMLType Storage

When we register an XML schema with Oracle XML DB, then by default tables are created and it is used to store XML instance documents associated with that schema and we can access this document or we can view that document in the Oracle XML DB repository. XMLType tables and columns are used to store large object values or we say that set of values. When we store the set of objects then we refer to structured or shredded storage. The data XMLType views can be stored in remote tables as well as local tables and remote tables are accessed with the help of database links.

XMLType and view uses B* tree, oracle text for index purpose as well as we can also use function-based index or bitmap index.

For accessing data in Oracle XML DB Repository we use different protocols as follows.

  1. HTTP protocol.
  2. WebDAV and FTP protocol server.
  3. Java Database Connectivity through Oracle Net Services.
  4. Oracle XML supports the data messaging oracle streams advanced query and web services.

We required a different API for XML as follows.

  1. XMLType: With the help of this API we can perform different operations on XMLType such as data validation and data transformation.
  2. Database URI types: This API is used for different URI types.
  3. DBMS_XDBT: it is used to create indexes in repository resources.
  4. DBMS_XDBZ: this API is used for repository-based security.
  5. DBMS_XMLPARSER: This API is used to implement DOM parser.
  6. DBMS_XMLQUERY: this API is used to provide database to XML type functionality.
  7. DBMS_XMLSTORE: This API is used to store the XML data in relational tables.
  8. DBMS_XSLPROCESSOR: this is used to implement XSLT processor.
  9. DBMS_XDB: This API is used to manage the Oracle XML DB repository resource.
  10. DBMS_XDB_VERSION: It is also used to manage the repository resources.
  11. DBMS_XMLDOM: it is used to implement the DOM API for XMLType.
  12. DBMS_XMLGEN: with help this API we can transform SQL query into the canonical XML format.
  13. DBMS_XMLSAVE: this API used to provide XML to database type functionality.
  14. DBMS_XMLSCHEMA: This API is used to manage the XML schemas.

XML Schema Catalog Views

USER_XML_SCHEMAS: This is used to register XML schemas owned by the user.

ALL_XML_SCHEMAS: This is used to register XML schemas in usable format by the current user.

DBA_XML_SCHEMAS: it is used to register schemas in Oracle XML DB.

Oracle XML DB Repository

Repository is a component of Oracle database that helps to handle XML data using a file or folder and it has the following properties as follows.

  • It is used to define the path and name.
  • It also has system-defined metadata such as owner and creation date as well as it also has user-defined metadata.

Examples

Now let’s see a different example 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.

oracle 1

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 insert into statement to insert records into the sample XML table by using 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.

row insert

We can use the following query for cross-checking as follows.

SELECT e.getClobVal() FROM sampleXML e;

Explanation

The end output of the above query we illustrate by using the following snapshot.

Oracle XML 2

Oracle stores the data in the CLOB column, which means it is a restriction on the LOB column to avoid this problem, to create tables by using an XML schema-based table. This schema map XML element with their object relation equivalent.

Rules and Regulations

  1. Basically, oracle XML is suitable for any type of application where all data is processed by using the XML.
  2. We required high-performance retrieval of XML documents.
  3. We must have high-performance indexing and searching of oracle XML documents.
  4. We set design parameters such as Data, Access, processing, storage, and Application language.
  5. If our data is not XML schema-based at that time we can use XMLType table or view.
  6. We need to define access models.
  7. We also need to define processing models.
  8. We need to define an Oracle XML storage option for the XMLType table and view.

Conclusion

We hope from this article you have understood about the Oracle XML. From this article, we have learned the basic syntax and we also see different examples. From this article, we learned how and when to use.

Recommended Articles

This is a guide to Oracle XML. Here we discuss the definition, syntax, types, How XML work in Oracle? and examples with code implementation respectively. You may also have a look at the following articles to learn more –

  1. Oracle Clone Database
  2. Oracle SYS_GUID()
  3. Oracle Tablespace
  4. Oracle INSTR()