How to generate XML data from Oracle database

There are a lot of sql/xml functions which can be used to convert the data in database to an XML document.We will just review few of them to get some idea of how is the conversion going on.

Log in to database and create a table

create table employees(employee_id varchar(20),f_name varchar(10),l_name varchar(20),dept varchar(20));

and insert some data also into it as shown below.




XMLElement sql/xml function--It is used to give xml instances from the data in database
Its syntax is

XMLElement ([name] identifier [,XML attribures] [,expression]..)

For example lets check out this example

SQL> select e.employee_id, XMLElement("Employee_name",e.f_name || e.l_name) as o
utput from employees e;

This will fetch us the following result

EMPLOYEE_ID
--------------------
OUTPUT
--------------------------------------------------------------------------------

100
<Employee_name>arpitrahi</Employee_name>

200
<Employee_name>ankitkumar</Employee_name>





As you can see we have got the name of the employee within xml tag.

If we want to add attributes to this we can use another built in command

XMLAttributes which is used to add Attributes to your XML document.

We will just modify our previous command to see how we can use it.


SQL> select e.employee_id, XMLElement("Employee_name" ,XMLAttributes(dept),e.f_n
ame || e.l_name) as output from employees e;


Now the output will be

EMPLOYEE_ID
--------------------
OUTPUT
--------------------------------------------------------------------------------

100
<Employee_name DEPT="integration">arpitrahi</Employee_name>

200
<Employee_name DEPT="andriod">ankitkumar</Employee_name>

As you can see the department is added as an attribute in the xml thus formed.




XMLForest() sql/xml function

It produces an XML element for each value expression specified in the list of arguments

Its general syntax is

XMLForest(value expression.....)

Eg-

SQL> select dept,XMLForest(employee_id,f_name,l_name) output from employees;

This will fetch us following output

DEPT
--------------------
OUTPUT
--------------------------------------------------------------------------------


integration
<EMPLOYEE_ID>100</EMPLOYEE_ID>
<F_NAME>arpit</F_NAME>
<L_NAME>rahi</L_NAME>

andriod
<EMPLOYEE_ID>200</EMPLOYEE_ID>
<F_NAME>ankit</F_NAME>
<L_NAME>kumar</L_NAME>

DEPT
--------------------
OUTPUT
--------------------------------------------------------------------------------





XMLAgg() sql/xml function

It is an aggregate function which is used to produce an aggregate of other sql/xml function

SQL> select XMLElement(dept,XMLAgg(XMLElement(employee_id,XMLForest(f_name,l_nam
e)))) output from employees;

It will fetch us following output.Well this is not the correct output but i am not sure whats wrong in here.


OUTPUT
--------------------------------------------------------------------------------

<DEPT><EMPLOYEE_ID><F_NAME>arpit</F_NAME><L_NAME>rahi</L_NAME></EMPLOYEE_ID><EMP

Ideally it should give me result
<DEPT><EMPLOYEE_ID><F_NAME>arpit</F_NAME><L_NAME>rahi</L_NAME></EMPLOYEE_ID><DEPT>




Well i have discussed this section because i was always amazed that why we always have xml data in our bpel process
even though in our database table are not in xml format ,so there must be some functions implemented somewhere in middle of
engine which is converting the non xml aggregated data in to xml format.This is not a perfect example but something like this must be going behind
the scene.This is just to give a feel that a lot of things goes behind the scene.