XML (eXtensible Markup Language) is a powerful way to represent structured data in a human-readable format, and Oracle Database offers a variety of tools for creating, manipulating, and validating XML directly within the database since the version 10g. Whether you’re dealing with complex documents or just storing structured data for application integration, XML capabilities in Oracle Database Server can make this process efficient. In this entry, we’ll explore how to create and validate XML directly from SQL, demonstrating the key features that make Oracle an effective XML storage and processing engine.
Creating XML Data from SQL
Creating XML from relational data in Oracle 19c is made simple with functions like XMLELEMENT, XMLAGG, and XMLFOREST. These functions allow you to convert rows and columns from tables into well-formed XML documents. Here is an example that demonstrates creating XML from relational data:
-- Create XML in a single query.
select
XMLElement("Employees" ,
xmlagg(
XMLELEMENT("Employee",
XMLFOREST(
e.EMPLOYEE_ID AS "EmployeeID",
e.FIRST_NAME AS "FirstName",
e.LAST_NAME AS "LastName",
(SELECT XMLAGG(
XMLELEMENT("Job",
XMLFOREST(
jh.START_DATE AS "StartDate",
jh.END_DATE AS "EndDate",
jh.JOB_ID AS "JobID",
jh.DEPARTMENT_ID AS "DepartmentID"
)
)
)
FROM job_history jh
WHERE jh.EMPLOYEE_ID = e.EMPLOYEE_ID
) AS "JobHistory"
) -- closing xmlforest
) -- closing xmlelement employee
) as "Employees" -- closing xmlagg
) Employees -- closing XMLElement employees
from employees e
where rownum < 4
;
<Employees><Employee><EmployeeID>199</EmployeeID><FirstName>Douglas</FirstName><LastName>Grant</LastName></Employee><Employee><EmployeeID>200</EmployeeID><FirstName>Jennifer</FirstName><LastName>Whalen</LastName><JobHistory><Job><StartDate>2005-09-17</StartDate><EndDate>2011-06-17</EndDate><JobID>AD_ASST</JobID><DepartmentID>90</DepartmentID></Job><Job><StartDate>2012-07-01</StartDate><EndDate>2016-12-31</EndDate><JobID>AC_ACCOUNT</JobID><DepartmentID>90</DepartmentID></Job></JobHistory></Employee><Employee><EmployeeID>201</EmployeeID><FirstName>Michael</FirstName><LastName>Martinez</LastName><JobHistory><Job><StartDate>2014-02-17</StartDate><EndDate>2017-12-19</EndDate><JobID>MK_REP</JobID><DepartmentID>20</DepartmentID></Job></JobHistory></Employee></Employees>
In this query:
XMLELEMENTis used to create an XML element with a given name. In this case, we use it to create both<Employees>and<Employee>elements.XMLFORESTconverts multiple columns into individual XML elements, each with a corresponding tag name (<EmployeeID>,<FirstName>, etc.). This function is used within the<Employee>element to create child elements for each employee’s details.XMLAGGis an aggregate function used to combine multiple XML fragments into a single XML document. In the example,XMLAGGis used to gather all<Employee>elements under the<Employees>root and also to gather job history information under each employee.XMLSERIALIZEis used to convert an XMLType instance into aCLOB,VARCHAR2, or other SQL data type, allowing it to be returned as a textual representation. This function is particularly useful when you need to convert XML data into a specific format for presentation or export.
As shown above, the output is an one-single line and unformatted XML type. We can format it in an indented multi-line output using XMLSERIALIZE:
-- Serialize and format the output.
select to_clob(xmlserialize( document
XMLElement("Employees" ,
xmlagg(
XMLELEMENT("Employee",
XMLFOREST(
e.EMPLOYEE_ID AS "EmployeeID",
e.FIRST_NAME AS "FirstName",
e.LAST_NAME AS "LastName",
(SELECT XMLAGG(
XMLELEMENT("Job",
XMLFOREST(
jh.START_DATE AS "StartDate",
jh.END_DATE AS "EndDate",
jh.JOB_ID AS "JobID",
jh.DEPARTMENT_ID AS "DepartmentID"
)
)
)
FROM job_history jh
WHERE jh.EMPLOYEE_ID = e.EMPLOYEE_ID
) AS "JobHistory"
) -- closing xmlforest
) -- closing xmlelement employee
) as "Employees" -- closing xmlagg
) -- closing XMLElement employees
as blob encoding 'UTF-8' INDENT SIZE = 2))
from employees e
where rownum < 4
;
This way we get a user-friendly text easier to read.
<?xml version="1.0" encoding="UTF-8"?>
<Employees>
<Employee>
<EmployeeID>199</EmployeeID>
<FirstName>Douglas</FirstName>
<LastName>Grant</LastName>
</Employee>
<Employee>
<EmployeeID>200</EmployeeID>
<FirstName>Jennifer</FirstName>
<LastName>Whalen</LastName>
<JobHistory>
<Job>
<StartDate>2005-09-17</StartDate>
<EndDate>2011-06-17</EndDate>
<JobID>AD_ASST</JobID>
<DepartmentID>90</DepartmentID>
</Job>
<Job>
<StartDate>2012-07-01</StartDate>
<EndDate>2016-12-31</EndDate>
<JobID>AC_ACCOUNT</JobID>
<DepartmentID>90</DepartmentID>
</Job>
</JobHistory>
</Employee>
<Employee>
<EmployeeID>201</EmployeeID>
<FirstName>Michael</FirstName>
<LastName>Martinez</LastName>
<JobHistory>
<Job>
<StartDate>2014-02-17</StartDate>
<EndDate>2017-12-19</EndDate>
<JobID>MK_REP</JobID>
<DepartmentID>20</DepartmentID>
</Job>
</JobHistory>
</Employee>
</Employees>
Validating XML in Oracle 19c
One of the powerful features Oracle offers is the ability to validate XML against an XML Schema (XSD). This helps ensure that the XML stored in the database conforms to a predefined structure, thereby preventing malformed data and ensuring data consistency. The XMLSchema feature in Oracle allows you to register an XML schema and use it for validation.
Let’s validate our XML against the following schema employees.xsd:
sh-4.2$ cat employees.xsd
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<!-- Root element of the XML document -->
<xs:element name="Employees">
<xs:complexType>
<xs:sequence>
<!-- Each Employee element -->
<xs:element name="Employee" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<!-- EmployeeID element: integer number -->
<xs:element name="EmployeeID" type="xs:int"/>
<!-- FirstName and LastName are simple strings -->
<xs:element name="FirstName" type="xs:string"/>
<xs:element name="LastName" type="xs:string"/>
<!-- Optional JobHistory element -->
<xs:element name="JobHistory" minOccurs="0">
<xs:complexType>
<xs:sequence>
<!-- Each Job element in the JobHistory -->
<xs:element name="Job" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<!-- StartDate and EndDate are of date type -->
<xs:element name="StartDate" type="xs:date"/>
<xs:element name="EndDate" type="xs:date"/>
<!-- JobID: a string that identifies the job -->
<xs:element name="JobID" type="xs:string"/>
<!-- DepartmentID: integer number, optional element -->
<xs:element name="DepartmentID" type="xs:int"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
First of all, we need to register the schema into the database.
-- Create directories
create or replace directory xml_dir as '/opt/oracle/xml';
create or replace directory xsd_dir as '/opt/oracle/xsd';
-- Check the existing directories.
select * from all_directories;
-- Grant read and write access to the user HR.
GRANT READ, WRITE ON DIRECTORY XML_DIR TO hr;
GRANT READ, WRITE ON DIRECTORY XSD_DIR TO hr;
-- register the schema.
BEGIN
DBMS_XMLSCHEMA.registerSchema(
schemaurl => 'http://db-master.com/employees.xsd',
schemadoc => XMLTYPE(bfilename('XSD_DIR', 'employees.xsd'), nls_charset_id('UTF-8')),
local => TRUE,
genTypes => FALSE,
genTables => FALSE
);
END;
/
-- List the registered schemas.
SELECT schema_url FROM user_xml_schemas;
-- Delete the schema.
BEGIN
DBMS_XMLSCHEMA.deleteschema(
schemaurl => 'http://db-master.com/employees.xsd',
delete_option => DBMS_XMLSCHEMA.delete_cascade_force);
END;
/
Now, we invoke XMLIsValid to validate that the output of our query conforms to the schema.
-- Create and validate the XML field
------------------------------------
select XMLIsValid (
XMLElement("Employees" ,
xmlagg(
XMLELEMENT("Employee",
XMLFOREST(
e.EMPLOYEE_ID AS "EmployeeID",
e.FIRST_NAME AS "FirstName",
e.LAST_NAME AS "LastName",
(SELECT XMLAGG(
XMLELEMENT("Job",
XMLFOREST(
jh.START_DATE AS "StartDate",
jh.END_DATE AS "EndDate",
jh.JOB_ID AS "JobID",
jh.DEPARTMENT_ID AS "DepartmentID"
)
)
)
FROM job_history jh
WHERE jh.EMPLOYEE_ID = e.EMPLOYEE_ID
) AS "JobHistory"
) -- closing xmlforest
) -- closing xmlelement employee
) as "Employees" -- closing xmlagg
) -- closing XMLElement employees
, 'http://db-master.com/employees.xsd') isvalid
from employees e
where rownum < 4
;
ISVALID
----------
1
In the next sample, we create the XML content which is dumped to a file after validating it.
-- Serializar, Formatear y Volcado si es valido XML formateado a fichero
----------------------------------------------------------
declare
v_Employees xmltype;
v_Employees_clob clob;
begin
select
XMLElement("Employees" ,
xmlagg(
XMLELEMENT("Employee",
XMLFOREST(
e.EMPLOYEE_ID AS "EmployeeID",
e.FIRST_NAME AS "FirstName",
e.LAST_NAME AS "LastName",
(SELECT XMLAGG(
XMLELEMENT("Job",
XMLFOREST(
jh.START_DATE AS "StartDate",
jh.END_DATE AS "EndDate",
jh.JOB_ID AS "JobID",
jh.DEPARTMENT_ID AS "DepartmentID"
)
)
)
FROM job_history jh
WHERE jh.EMPLOYEE_ID = e.EMPLOYEE_ID
) AS "JobHistory"
) -- closing xmlforest
) -- closing xmlelement employee
) as "Employees" -- closing xmlagg
) -- closing XMLElement employees
into v_Employees
from employees e
where rownum < 4
;
if v_employees.isSchemaValid ('http://db-master.com/employees.xsd') = 1 then
dbms_output.put_line ('XML document is VALID and being dumped to file');
select to_clob(xmlserialize( document v_employees as blob encoding 'UTF-8' INDENT SIZE = 2)) into v_Employees_clob
from dual;
dump_clob_to_file(
p_directory => 'XML_DIR',
p_filename => 'employees.xml',
p_content => v_Employees_clob
);
else
dbms_output.put_line ('XML document is INVALID => '||sqlerrm);
end if;
end;
/
To dump the content of the CLOB to a file we are using the following procedure:
CREATE OR REPLACE PROCEDURE dump_clob_to_file(
p_directory IN VARCHAR2,
p_filename IN VARCHAR2,
p_content IN CLOB
) AS
v_file_handle UTL_FILE.file_type;
v_chunk_size NUMBER DEFAULT 32000;
v_position NUMBER DEFAULT 1;
v_clob_length NUMBER DEFAULT NVL(DBMS_LOB.getlength(p_content), 0);
BEGIN
-- Open the file for writing in the specified directory
v_file_handle := UTL_FILE.fopen(p_directory, p_filename, 'w', 32760);
-- Loop through the CLOB and write to the file in chunks
WHILE v_position < v_clob_length
LOOP
UTL_FILE.put(v_file_handle, DBMS_LOB.substr(p_content, v_chunk_size, v_position));
UTL_FILE.fflush(v_file_handle); -- Ensure buffer is written to the file
v_position := v_position + v_chunk_size;
END LOOP;
-- Add a new line at the end of the file
UTL_FILE.new_line(v_file_handle);
-- Close the file
UTL_FILE.fclose(v_file_handle);
END dump_clob_to_file;
/
To wrap up
Oracle Database support for XML is a powerful feature for organizations dealing with semi-structured data. The combination of XML creation functions, XMLType data storage, schema validation, and robust querying capabilities makes Oracle an excellent choice for managing XML data in an enterprise environment. Whether you are building web services, integrating diverse systems, or just need to store complex data structures, Oracle offers a flexible and powerful XML solution.
Python Programming: A Smarter And Faster Way To Learn Python In 7 Days: With Practical Exercises, Interview Questions, Tips And Tricks
$16.49 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Learn SQL in less than 20 hours: Understand the Language and Do It Yourself
$9.99 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
In the Company of Giants: Candid Conversations With the Visionaries of the Digital World
$26.97 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)