SQL & PL/SQL

Hi All,

I am new to this network. I am also new to oracle XML package. I want a help for the below query.

CREATE TABLE EMP(ID NUMBER PRIMARY KEY, NAME VARCHAR2(10), PHONE NUMBER);

INSERT INTO EMP(ID,NAME,PHONE) VALUES (11,'Joy',1234);
INSERT INTO EMP(ID,NAME,PHONE) VALUES (22,'Mike',5678);
INSERT INTO EMP(ID,NAME,PHONE) VALUES (33,'Jason',NULL);
COMMIT;

I want to export the EMP table data in a XML file with the below format.

Required Output:

<?xml version="1.0" encoding="UTF-8"?><STATICDATA><EMP><ID>11</ID><NAME>Joy</NAME></EMP></STATICDATA>
<?xml version="1.0" encoding="UTF-8"?><STATICDATA><EMP><ID>22</ID><NAME>Mike</NAME></EMP></STATICDATA>
<?xml version="1.0" encoding="UTF-8"?><STATICDATA><EMP><ID>33</ID><NAME>Jason</NAME></EMP></STATICDATA>

I have used some XML functions and have written the below query.

select XMLROOT(XMLELEMENT(staticdata,XMLELEMENT(EMP,XMLELEMENT(ID,ID),XMLELEMENT(NAME,NAME))), version '1.0" encoding="UTF-8') xml FROM EMP;

output of my query:

<?xml version="1.0" encoding="UTF-8"?>
<STATICDATA>
<EMP>
<ID>11</ID>
<NAME>Joy</NAME>
</EMP>
</STATICDATA>
<?xml version="1.0" encoding="UTF-8"?>
<STATICDATA>
<EMP>
<ID>22</ID>
<NAME>Mike</NAME>
</EMP>
</STATICDATA>
<?xml version="1.0" encoding="UTF-8"?>
<STATICDATA>
<EMP>
<ID>33</ID>
<NAME>Jason</NAME>
</EMP>
</STATICDATA>


But i want the out as the required output above. every record in a single line. can any one help me in achieving the required output. also can i export all the columns of the table by some thing like select * from the table in XML file ?

Thanks,
Sartaj