Generating and Exporting XML File in Oracle
In this post, we will discuss the alternative method for converting a table stored in an Oracle database to XML format and exporting it as an XML file.
In some cases, there may be a need to write the data stored in traditional databases to XML files. Iin Oracale, there are some XML functions for this subject, however, in this post, we'll discuss the alternative method for converting a table stored in an Oracle database to XML format within an Oracle database and exporting it as an XML file, without using Oracle's XML functions.
You can use that method in stored procedures in Oracle. Assume we have a table called Customer, which has the following columns:
CREATE TABLE customer
(
id NUMBER,
first_name VARCHAR2(100),
last_name VARCHAR2(100),
birth_date DATE,
gender VARCHAR2(1)
)
First of all, we must convert the data of the table to XML format. We should create a string by concatenating the table columns between tags in XML format in SQL query. We can write this formatted data into another table for storage and export.
Here is an example:
CREATE TABLE customer_xml
(
created_date DATE,
row_num NUMBER,
xml_data VARCHAR2(4000)
);
INSERT INTO customer_xml
(created_date,
row_num,
xml_data)
SELECT c.created_date,
c.row_num,
CASE
WHEN c.row_num = 1 THEN
'<?xml version="1.0" encoding="UTF-8"?><root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
ELSE
''
END ||
'<id>' || TO_CHAR(id) || '</id>' ||
'<first_name>' || first_name || '</first_name>' ||
'<last_name>' || last_name || '</last_name>' ||
'<birth_date>' || TO_CHAR(birth_date, 'YYYY-MM-DD') || '</birth_date>' ||
'<gender>' || gender || '</gender>' ||
CASE
WHEN c.row_num = rc.row_count THEN
'</root>'
ELSE
''
END AS xml_data
FROM (SELECT TRUNC(SYSDATE) AS created_date,
c.*,
ROW_NUMBER() OVER(ORDER BY c.id) AS row_num
FROM customer c) c
LEFT JOIN (SELECT TRUNC(SYSDATE) AS created_date,
COUNT(*) AS row_count
FROM customer c) rc
ON c.created_date = rc.created_date;
COMMIT;
After the XML formatted data is inserted into the table, we can export the file with XML extension. To do that we can use the UTL_FILE package in Oracle. To export the data from the customer_xml table, we can use UTF_FILE.fopen_nchar function. To create an XML file, it is sufficient to add the ".xml" extension to the end of the file name parameter.
After that, the XML file will be created at the location specified in the UTL_FILE function:
BEGIN
fo := UTL_FILE.fopen_nchar (location, filename || '.xml', 'w', 32767);
FOR c1 IN (SELECT xml_data
FROM customer_xml
ORDER BY row_num)
LOOP
UTL_FILE.put_line_nchar (fo, C1.xml_data);
END LOOP;
UTL_FILE.fclose (fo);
END;This is it! What do you think? Please share in the comments below.
Opinions expressed by DZone contributors are their own.