Generate XML output format from table in Oracle

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.