Generating and Validating XML from Oracle DB.

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:

• XMLELEMENT is used to create an XML element with a given name. In this case, we use it to create both <Employees> and <Employee> elements.
• XMLFOREST converts 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.
• XMLAGG is an aggregate function used to combine multiple XML fragments into a single XML document. In the example, XMLAGG is used to gather all <Employee> elements under the <Employees> root and also to gather job history information under each employee.
• XMLSERIALIZE is used to convert an XMLType instance into a CLOB, 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.