Working with XML in Oracle

Oracle has nice features to export data as an XML file. Let us discuss a few of them.

The first one is to use SQL Developer. We could already export a table in SQL developer as an XML file.

The second one is to use the DBMS_XMLGEN package. This allows to use queries in an export. Let me show some code to demonstrate this.

SELECT DBMS_XMLGEN.GETXML(
'SELECT EMPLOYEE_ID
, LAST_NAME
, DEPARTMENT_ID
FROM employees
WHERE DEPARTMENT_ID < 101')
"XML OUTPUT"
FROM dual;

This creates output that can be inserted into an XML file. This can be read by a XML reader, such as Altova XMLSpy:

XML

A third possibility is to work with a stored procedure. The code can be found here.

One may notice that this stored procedure also uses the DBMS_XMLGEN.GETXML package.

A fourth possibility is to use this statement:

select xmlelement( "Emp", xmlforest( first_name, last_name, salary ) ) from hr.employees;

To get this displayed properly in sqlplus, we need to issue three commands:

set serveroutput on
set pages 50000
set long 100000000

This leads to a nice readable result:

nice readable result

Then possibility number 5. It is interesting to see that one has many possibilities to write a table or a query as XML. I read this possibility:

select dbms_xmlgen.getxmltype('select * from employees') from dual;