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:
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:
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;