PL/SQL XML - Oracle PL/SQL Tutorial

XML (Extensible Markup Language) is a popular format used for data exchange and storage. In Oracle PL/SQL, XML processing is fully supported through the XML DB technology, which allows developers to easily manipulate and store XML documents.

To work with XML in Oracle PL/SQL, you can use the XMLType data type, which is a binary representation of an XML document. This data type provides several built-in methods that allow developers to extract and manipulate the contents of an XML document.

One of the most common ways to work with XML in Oracle PL/SQL is to use the XMLType constructor function. This function takes an XML document as input and returns an XMLType instance that can be used in PL/SQL code. For example, the following code creates an XMLType instance from an XML document:

DECLARE
  l_xml XMLType;
BEGIN
  l_xml := XMLType('<book><title>My Book</title><author>John Doe</author></book>');
END;

Once you have an XMLType instance, you can use various built-in methods to extract data from the XML document. For example, the EXTRACTVALUE method allows you to extract a single value from an XML document. The following code extracts the value of the “title” element from the XML document created in the previous example:

DECLARE
  l_xml XMLType := XMLType('<book><title>My Book</title><author>John Doe</author></book>');
  l_title VARCHAR2(4000);
BEGIN
  select EXTRACTVALUE(l_xml, '/book/title') into l_title from dual;
  DBMS_OUTPUT.put_line('Title: ' || l_title);
END;

In addition to extracting values, you can also use the XMLType data type to modify XML documents. For example, the UPDATEXML method allows you to update the value of a specific element in an XML document. The following code updates the value of the “title” element in the XML document:

UPDATE books 
SET xml_data = UPDATEXML(xml_data, '/book/title/text()','My Book 1')
WHERE id = 1;

Finally, you can also use the XMLType data type to store XML documents in the database. Oracle provides several storage options for XML documents, including XMLType columns in tables and XMLType variables in PL/SQL code. To store an XML document in an XMLType column, you simply need to insert the XML document into the column using standard SQL syntax. For example, the following code creates a table with an XMLType column and inserts an XML document into the column:

CREATE TABLE books (
  id NUMBER,
  xml_data XMLType
);

INSERT INTO books (id, xml_data) VALUES 
(1, XMLType('<book><title>My Book</title><author>John Doe</author></book>'));

Learn PL/SQL