How to get an Oracle table as a XML Document using XMLType


Here is the table creation script;

CREATE TABLE EMPLOYEE
(
ID               NUMBER,
EMPLOYEENAME     VARCHAR2(20 BYTE),
EMPLOYEESURNAME  VARCHAR2(20 BYTE),
GENDER           VARCHAR2(20 BYTE),
SALARY           VARCHAR2(5 BYTE),
EMPLOYEEID       VARCHAR2(20 BYTE)
)

And lets enter some data inside it;

INSERT INTO EMPLOYEE (
ID, EMPLOYEENAME, EMPLOYEESURNAME,
GENDER, SALARY, EMPLOYEEID)
VALUES ( 1, 'tuna' , 'tore' ,
'M',  '1000', 1)
INSERT INTO EMPLOYEE (
ID, EMPLOYEENAME, EMPLOYEESURNAME,
GENDER, SALARY, EMPLOYEEID)
VALUES ( 2, 'linus' , 'torvalds' ,
'M',  '1000', 2)
INSERT INTO EMPLOYEE (
ID, EMPLOYEENAME, EMPLOYEESURNAME,
GENDER, SALARY, EMPLOYEEID)
VALUES ( 3, 'bill' , 'gates' ,
'M',  '1000', 3)

By using XMLType Oracle build-in function we can convert the whole table and get the data inside as a XML document in the following example;

SELECT
XMLTYPE
(
CURSOR
(
SELECT * FROM EMPLOYEE
)
)
FROM
DUAL;

Here is the result;

<?xml version="1.0"?>
<ROWSET>
<ROW>
<ID>1</ID>
<EMPLOYEENAME>tuna</EMPLOYEENAME>
<EMPLOYEESURNAME>tore</EMPLOYEESURNAME>
<GENDER>M</GENDER>
<SALARY>1000</SALARY>
<EMPLOYEEID>1</EMPLOYEEID>
</ROW>
<ROW>
<ID>2</ID>
<EMPLOYEENAME>linus</EMPLOYEENAME>
<EMPLOYEESURNAME>torvalds</EMPLOYEESURNAME>
<GENDER>M</GENDER>
<SALARY>1000</SALARY>
<EMPLOYEEID>2</EMPLOYEEID>
</ROW>
<ROW>
<ID>3</ID>
<EMPLOYEENAME>bill</EMPLOYEENAME>
<EMPLOYEESURNAME>gates</EMPLOYEESURNAME>
<GENDER>M</GENDER>
<SALARY>1000</SALARY>
<EMPLOYEEID>3</EMPLOYEEID>
</ROW>
</ROWSET>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s