Oracle provides plenty of XML generation methods, I’ll try to demonstrate two of them: SYS_XMLGEN and DBMS_XMLQUERY.
I’ll start with creating a simple table and fill it with sample data:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE customers ( customer_name VARCHAR2 (30), phone VARCHAR2(11)); INSERT INTO customers VALUES ( 'GOKHAN', '5421000100' ); INSERT INTO customers VALUES ( 'JACK', '5426661010' ); INSERT INTO customers VALUES ( 'JHON', '5352004050' ); COMMIT; |
To be able to use SYS_XMLGEN, I’ll create an object to map our data:
|
1 2 3 |
CREATE OR REPLACE TYPE customers_map_type AS OBJECT ( customer_name VARCHAR2 (30), phone VARCHAR2(11)); |
Now I can easily convert my data to XML:
|
1 2 3 |
SELECT SYS_XMLGEN (customers_map_type (customer_name, phone)).getclobval() xml_result FROM customers; |
It will convert each row as a separate XML, so I’ll use SYS_XMLAGG to aggregate all rows:
|
1 2 3 |
SELECT SYS_XMLAGG (SYS_XMLGEN (customers_map_type (customer_name, phone))).getclobval() xml_result FROM customers |
If I can use UTL_FILE to write this output to a file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE DIRECTORY test_dir AS '/oracle'; DECLARE f UTL_FILE.file_type; BEGIN f := UTL_FILE.fopen ('TEST_DIR', 'test.xml', 'w'); FOR c1_rec IN (SELECT SYS_XMLAGG (SYS_XMLGEN (customers_map_type (customer_name, phone))).getclobval() xml_result FROM customers) LOOP UTL_FILE.put (f, c1_rec.xml_result); END LOOP; UTL_FILE.fclose (f); END; / |
Here’s the content of the “test.xml” file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?xml version="1.0"?> <ROWSET> <ROW> <CUSTOMER_NAME>GOKHAN</CUSTOMER_NAME> <PHONE>5421000100</PHONE> </ROW> <ROW> <CUSTOMER_NAME>JACK</CUSTOMER_NAME> <PHONE>5426661010</PHONE> </ROW> <ROW> <CUSTOMER_NAME>JHON</CUSTOMER_NAME> <PHONE>5352004050</PHONE> </ROW> </ROWSET> |
As you see, it’s very easy to generate XML file from the Oracle databases with using SYS_XMLGEN. On the other hand, DBMS_XMLQUERY package provides much more simply way to generate XML:
|
1 2 |
SELECT DBMS_XMLQUERY.getxml('select * from customers') FROM DUAL; |
Here’s the result:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?xml version = '1.0'?> <ROWSET> <ROW num="1"> <CUSTOMER_NAME>GOKHAN</CUSTOMER_NAME> <PHONE>5421000100</PHONE> </ROW> <ROW num="2"> <CUSTOMER_NAME>JACK</CUSTOMER_NAME> <PHONE>5426661010</PHONE> </ROW> <ROW num="3"> <CUSTOMER_NAME>JHON</CUSTOMER_NAME> <PHONE>5352004050</PHONE> </ROW> </ROWSET> |
Please share
Ashish
Gokhan Atil