PL/SQL XMLType – Oracle PL/SQL Tutorial

PL/SQL XMLType is a data type in the PL/SQL programming language that is specifically designed to handle XML data. It provides a set of functions and operators that allow developers to manipulate and extract information from XML documents in a convenient and efficient way.

With PL/SQL XMLType, developers can store XML data as a column in a database table, pass XML data as a parameter to a stored procedure or function, and return XML data as the result of a query or function call.

One of the key advantages of PL/SQL XMLType is its ability to validate XML documents against a schema. The XMLType data type provides a validate function that can be used to ensure that an XML document conforms to a specific schema. This is particularly useful in applications where data integrity is critical.

Syntax

The syntax for defining an XMLType variable in PL/SQL is as follows:

DECLARE
  my_xml XMLTYPE;
BEGIN
  my_xml := XMLTYPE('<root><element>value</element></root>');
END;

In this syntax, we define a variable called my_xml of type XMLType and assign it the value of an XML document. Once you have an XMLType variable, you can use various functions to manipulate it.

Example

Here’s an example of how to use Oracle PL/SQL XMLType. Let’s assume that we have a table called “employees” with the following structure:

CREATE TABLE employees (
  employee_id   NUMBER(6),
  first_name    VARCHAR2(20),
  last_name     VARCHAR2(25),
  email         VARCHAR2(25),
  hire_date     DATE,
  job_id        VARCHAR2(10),
  salary        NUMBER(8,2),
  department_id NUMBER(4)
);

Suppose we have an XML file named “employees.xml” that contains data for the “employees” table. We can load this data into the table using the following PL/SQL code:

DECLARE
  xml_file XMLType := XMLType( 
  '<?xml version="1.0" encoding="UTF-8"?>
  <employees>
    <employee>
      <employee_id>100</employee_id>
      <first_name>Steven</first_name>
      <last_name>King</last_name>
      <email>SKING</email>
      <hire_date>2003-06-17</hire_date>
      <job_id>AD_PRES</job_id>
      <salary>24000</salary>
      <department_id>90</department_id>
    </employee>
    <employee>
      <employee_id>101</employee_id>
      <first_name>Neena</first_name>
      <last_name>Kochhar</last_name>
      <email>NKOCHHAR</email>
      <hire_date>2005-09-21</hire_date>
      <job_id>AD_VP</job_id>
      <salary>17000</salary>
      <department_id>90</department_id>
    </employee>
  </employees>'
  );
BEGIN
  FOR emp IN (SELECT ExtractValue(Value(p), '/employee/employee_id') AS employee_id,
                     ExtractValue(Value(p), '/employee/first_name') AS first_name,
                     ExtractValue(Value(p), '/employee/last_name') AS last_name,
                     ExtractValue(Value(p), '/employee/email') AS email,
                     To_Date(ExtractValue(Value(p), '/employee/hire_date'), 'YYYY-MM-DD') AS hire_date,
                     ExtractValue(Value(p), '/employee/job_id') AS job_id,
                     ExtractValue(Value(p), '/employee/salary') AS salary,
                     ExtractValue(Value(p), '/employee/department_id') AS department_id
              FROM TABLE(XMLSequence(xml_file.extract('//employee'))) p)
  LOOP
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
    VALUES (emp.employee_id, emp.first_name, emp.last_name, emp.email, emp.hire_date, emp.job_id, emp.salary, emp.department_id);
  END LOOP;
  COMMIT;
END;
/

This code creates an XMLType variable named “xml_file” that contains the XML data from the “employees.xml” file. It then uses the XMLSequence function to extract the “employee” nodes from the XML data and loops through them. Inside the loop, it uses the EXTRACTVALUE function to extract the values of the child elements of each “employee” node and inserts them into the “employees” table.

Note that the code also converts the hire_date value from a string to a date using the TO_DATE function.

Once the code is executed, the “employees” table will be populated with the data from the “employees.xml” file.

PL/SQL XMLType also provides a set of functions for querying and manipulating XML data. For example, the extract function can be used to extract a specific element or attribute from an XML document, while the existsNode function can be used to determine whether a particular node exists in an XML document.

Another useful feature of PL/SQL XMLType is its ability to convert XML data to other formats, such as JSON or relational data. This can be done using the xmlserialize function, which can convert an XML document to a string in a specific format.

In conclusion, PL/SQL XMLType is a powerful data type that provides a convenient and efficient way to handle XML data in PL/SQL applications. Its ability to validate XML documents, query and manipulate XML data, and convert XML data to other formats makes it a valuable tool for developers working with XML data.

Syntax

Example

Learn PL/SQL