Generate XML output format from table in Oracle
Oracle provide different function for generating table output in XML format.
We can do order by and group by with help of function XMLAGG. It is used full which need output in XML format.
XMLElement
XMLELEMENT function used to generate output of columns in xml format.
SQL> SELECT XMLElement("HireDate", hire_date) FROM hr.employees WHERE employee_id = 100;
XMLELEMENT("HIREDATE",HIRE_DATE)
---------------------------------
2003-06-17
-- Use XMLElement as nested example:
SQL> SELECT XMLElement("Emp",XMLElement("name", first_name), XMLElement("hiredate", hire_date)) AS "RESULT"
FROM employees WHERE employee_id = 100;
RESULT
------------------------------------------------------------------
Steven2003-06-17
XML Attribute
Use the XMLElement for define XMLAttributes in it.
SQL> SELECT XMLElement("Emp", XMLAttributes( employee_id as "ID"),first_name) AS "RESULT" FROM hr.employees e WHERE employee_id = 200;
RESULT
--------------------------------------------------------------------------------
Jennifer
XML Forest
XML forest automatic generate element as column name and quick to use as in XMLElement need to define.
select xmlelement( "Emp", xmlforest( employee_id, first_name ) ) from employees WHERE employee_id = 100;
XMLELEMENT("EMP",XMLFOREST(EMPLOYEE_ID,FIRST_NAME))
------------------------------------------------------------------------
100Steven
XMLCONCAT
XMLConcat is used to concatenate the column in one column as result.
SELECT XMLConcat(XMLElement("first", e.first_name),XMLElement("last", e.last_name)) AS "RESULT" FROM employees e where employee_id = 100;
RESULT
------------------------------------------------------------
StevenKing
XMLAGG
It is used for group by or order by on based of column elements.
--Simple use the group count in XML with count aggregate function
SELECT XMLElement("Department", XMLAttributes(department_id AS "deptno")) as department_no,
XMLElement("Empcount", count(*)) as emp_count FROM hr.employees e where department_id in ('30','40')
GROUP BY e.department_id;
DEPARTMENT_NO EMP_COUNT ------------------------------------- ----------------------- 6 1
--If we want the list of employees name in xml format:
SELECT XMLElement("Department", XMLAttributes(department_id AS "deptno"),
XMLAgg(XMLElement("Employee", e.first_name||' '||e.last_name)))
AS "Department List"
FROM hr.employees e where department_id in ('30','40')
GROUP BY e.department_id;
Department List
------------------------------------------
Den Raphaely
Karen Colmenares
Guy Himuro
Sigal Tobias
Shelli Baida
Alexander Khoo
Susan Mavris
--We can sort the name also with XMLAGG
SELECT XMLElement("Department", XMLAttributes(department_id AS "deptno"),
XMLAgg(XMLElement("Employee", e.first_name||' '||e.last_name) order by e.first_name))
AS "Department List"
FROM hr.employees e where department_id in ('30','40')
GROUP BY e.department_id;
Department List
----------------------------------------------------------------------
Alexander Khoo
Den Raphaely
Guy Himuro
Karen Colmenares
Shelli Baida
Sigal Tobias
Susan Mavris
Smart way of Technology 