Generating Native XML using XML SQL Utility(XSU)

We will further see some XSU function.

SYS_XMLGEN() function

It converts its argument into an XML document.It takes object or XMLType as argument

and returs an XMLType output.

SQL> select SYS_XMLGEN(employee_id) output from employees;

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

<?xml version="1.0"?>
<EMPLOYEE_ID>100</EMPLOYEE_ID>

<?xml version="1.0"?>
<EMPLOYEE_ID>200</EMPLOYEE_ID>






We can also define XMLFOrest inside this function



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

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

<?xml version="1.0"?>
<ROW>
<F_NAME>arpit</F_NAME><L_NAME>rahi</L_NAME></ROW>

<?xml version="1.0"?>
<ROW>
<F_NAME>ankit</F_NAME><L_NAME>kumar</L_NAME></ROW>



so if we will look at this output we will know that it operates on column within each row and generate and XML document.As you can see the xml thus formed were inside the row tag that comes by default.If we want to change it we can use another XSU function
that is XMLFormat() which is used to enclose your xml document within the tag which you will specify in XMLFormat.It will be more clear with this example.


SQL> select SYS_XMLGEN(XMLForest(f_name,l_name),XMLFormat('NAME')) output from e
mployees;

OUTPUT
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<NAME>
<F_NAME>arpit</F_NAME><L_NAME>rahi</L_NAME></NAME>

<?xml version="1.0"?>
<NAME>
<F_NAME>ankit</F_NAME><L_NAME>kumar</L_NAME></NAME>

Now you can see the xml is tagged inside name





But these document generated are still not proper xml document as we are having two xml document getting generated so we will see some other function which will create a proper xml document.


SYS_XMLAGG() function

It is an aggregated fucntion which groups all the input and produces a sinlge XML document.

SQL> select SYS_XMLAGG(SYS_XMLGEN(XMLForest(f_name,l_name),XMLFormat('NAME'))) o
utput from employees group by employee_id;

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

<?xml version="1.0"?>
<ROWSET>
<NAME>
<F_NAME>arpit</F_NAME>
<L_NAME>rahi</L_NAME>
</NAME>
</ROWSET>

<?xml version="1.0"?>
<ROWSET>
<NAME>
<F_NAME>ankit</F_NAME>
<L_NAME>kumar</L_NAME>
</NAME>
</ROWSET>

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