Why do we need XML functions?

XML is simple, flexible and platform-independent as a reason it is used as a communication language between different technology applications. In today s market most of the integration tools use XML as basis.

For some integration needs customers using Oracle database may need to extract XML from the DB table data. For this Oracle has provided various XML functions to convert table data into XML.

Following are some of the Oracle SQL XML functions that are frequently used:

• XMLELEMENT
• XMLATTRIBUTES
• XMLFOREST
• XMLAGG
• XMLROOT
• XMLCDATA

XMLELEMENT:- The XMLELEMENT function is the basic unit for turning column data into XML fragm ents. In the following example, the first parameter specifies the tag name to be used and the second specifies the column that will supply the data contained wi thin the tag.

SELECT XMLELEMENT("name", e.ename) AS emp_name
  FROM scott.emp e
 WHERE e.empno = 7788;

EMP_NAME
------------------------------------
<name>SCOTT</name>

1 ROW selected.


The XMLELEMENT function can also be used to group together and place a tag around existing XML fragments.

SELECT XMLELEMENT("employee", XMLELEMENT("emp_number", e.empno), XMLELEMENT("emp_name", e.ename) ) AS emp_details
  FROM scott.emp e
 WHERE e.empno = 7788;
 
  EMP_DETAILS
  --------------------------------------------------------------------------------------------------
  <employee> <emp_number>7788</emp_number> <emp_name>SCOTT</emp_name> </employee>


XMLATTRIBUTES:- The XMLATTRIBUTES function adds attributes to the parent element. We use XMLATTRIBUTES function inside XMLELEMENT function. In XMLATTRIBUTES we can pass one or more columns in a comma separated list. The attribute names will be column names  respectively if alias is not used.

SELECT XMLELEMENT("employee", XMLATTRIBUTES( e.empno , e.ename) ) AS employee
  FROM scott.emp e
 WHERE e.empno = 7788;

 EMPLOYEE
 --------------------------------------------------------------------------------------------------
 <employee EMPNO="7788" ENAME="SCOTT"> </employee>
 

 Another example to show parent XMLELEMENT can contain both attributes (using ali as) and child tags.

 SELECT XMLELEMENT("employee", XMLATTRIBUTES(e.empno AS "employee_number"),
        XMLELEMENT("name",e.ename), XMLELEMENT("job",e.job) ) AS employee
   FROM scott.emp e
  WHERE e.empno = 7788;

 EMPLOYEE
 --------------------------------------------------------------------------------------------------
 <employee employee_number="7788"> <name>SCOTT</name> <job>ANALYST</job> </employee>
 
 XMLFOREST:- It gets difficult if XMLELEMENT has to deal with lots of columns. Like XMLATTRIBUTES, the XMLFOREST function allows you to process multiple columns at once.

 SELECT XMLELEMENT("employee",
         XMLFOREST( e.empno AS "employee_number", e.ename AS "name", e.job AS "job") )
         AS employee
   FROM scott.emp e
  WHERE e.empno = 7788;

EMPLOYEE
--------------------------------------------------------------------------------------------------
<employee> <employee_number>7788</employee_number> <name>SCOTT</name> <job>ANALYST</job> </employee>



XMLAGG:- XMLAGG is xml aggregate function used to aggregate multiple separate fragments into a single fragment. Lets first see the need of XMLAGG function. Till now we have run xml functions for single records. What happens if we start dealing with multiple rows of data?

SELECT XMLELEMENT("employee",
          XMLFOREST( e.empno AS "employee_number", e.ename AS "name") )
          AS employees
  FROM scott.emp e
 WHERE e.deptno = 10;

 EMPLOYEES
--------------------------------------------------------------------------------------------------
 <employee><employee_number>7782</employee_number><name>CLARK</name></employee> <employee><employee_number>7839</employee_number><name>KING</name></employee> <employee><employee_number>7934</employee_number><name>MILLER</name></employee>

 3 ROWS selected.


 Output displays three xml fragments in three separate rows. XMLAGG function allows us to aggregate these separate fragments into a single fragment. In the following example we can see the three fragments are now presente d in a single row.

SELECT XMLAGG(XMLELEMENT("employee",
                XMLFOREST( e.empno AS "employee_number", e.ename AS "name")))
                AS employees
   FROM scott.emp e
  WHERE e.deptno = 10;

  EMPLOYEE
  --------------------------------------------------------------------------------------------------
<employee><employee_number>7782</employee_number><name>CLARK</name></employee> <employee><employee_number>7839</employee_number><name>KING</name></employee><employee><employee_number>7934</employee_number><name>MILLER</name></employee>

1 ROW selected.

Without a root (base) tag, this is not a well formed document, so we must surround it in an XMLELEMENT to provide the root tag.

SELECT XMLELEMENT("employee_details",
           XMLAGG(XMLELEMENT("employee",
                   XMLFOREST(e.empno AS "employee_number", e.ename AS "name"))))
               AS employees
  FROM scott.emp e
 WHERE e.deptno = 10;

EMPLOYEE
--------------------------------------------------------------------------------------------------
 <employee_details><employee><employee_number>7782</employee_number><name>CLARK</name></employee><employee><employee_number>7839</employee_number><name>KING</name></employee><employee><employee_number>7934</employee_number><name>MILLER</name></employee></employee_details>

1 ROW selected.