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.
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.
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.