Making XML tags dynamic in SQL and PL/SQL

While trying to produce XML using Oracle's native XML functions, I needed some of the XML tags to be dynamic.  To simplify and illustrate the problem that I encountered, I'll show an example that uses the time-tested, traditional EMP and DEPT tables.  

Let's say that we need to produce something like this for all departments.

<departments>
 <accounting>
  <employee>Clark</employee>
  <employee>King</employee>
  <employee>Miller</employee>
 </accounting>
 ...
</departments>

Let's start with a short SQL to get an aggregated employee list for each department.  The result is four rows of xmltype - one for each department

 select xmlelement("department", 
           xmlagg(xmlelement("employee", initcap(e.ename))))
   from dept d
   left outer join emp e on (e.deptno = d.deptno)
   group by d.deptno;
Result (4 rows):
<department>
 <employee>King</employee>
 <employee>Miller</employee>
 <employee>Clark</employee>
</department>
<department>
 <employee>Jones</employee>
 <employee>Adams</employee>
 <employee>Smith</employee>
 <employee>Ford</employee>
 <employee>Scott</employee>
</department>
<department>
 <employee>Blake</employee>
 <employee>James</employee>
 <employee>Turner</employee>
 <employee>Martin</employee>
 <employee>Ward</employee>
 <employee>Allen</employee>
</department>
<department>
 <employee></employee>
</department>

Next we'll aggregate these inside a single outer tag called "departments"

 select xmlelement("departments",
           xmlagg(
              xmlelement("department", 
                 xmlagg(xmlelement("employee", initcap(ename))))))
   from dept d
   left outer join emp e on (e.deptno = d.deptno)
  group by d.deptno, d.dname;
Result:
<departments>
 <department>
  <employee>King</employee>
  <employee>Miller</employee>
  <employee>Clark</employee>
 </department>
 <department>
  <employee>Jones</employee>
  <employee>Adams</employee>
  <employee>Smith</employee>
  <employee>Ford</employee>
  <employee>Scott</employee>
 </department>
 <department>
  <employee>Blake</employee>
  <employee>James</employee>
  <employee>Turner</employee>
  <employee>Martin</employee>
  <employee>Ward</employee>
  <employee>Allen</employee>
 </department>
 <department>
  <employee></employee>
 </department>
</departments>

Now we'll try to change the <department> tag to have the value of the actual department name by replacing xmlelement("department",  with xmlelement(dname,   and we'll see that the value of dname doesn't appear

 select xmlelement("departments",
           xmlagg(
              xmlelement(dname, 
                 xmlagg(xmlelement("employee", initcap(ename))))))
   from dept d
   left outer join emp e on (e.deptno = d.deptno)
  group by d.deptno, d.dname;
Result:
<departments>
 <DNAME>
  <employee>King</employee>
  <employee>Miller</employee>
  <employee>Clark</employee>
 </DNAME>
 <DNAME>
  <employee>Jones</employee>
  <employee>Adams</employee>
  <employee>Smith</employee>
  <employee>Ford</employee>
  <employee>Scott</employee>
 </DNAME>
 <DNAME>
  <employee>Blake</employee>
  <employee>James</employee>
  <employee>Turner</employee>
  <employee>Martin</employee>
  <employee>Ward</employee>
  <employee>Allen</employee>
 </DNAME>
 <DNAME>
  <employee></employee>
 </DNAME>
</departments>

So, as we can see, the value of the dname column has not been interpreted and used for the tag.  The query is simply using the string DNAME instead.   The problem now becomes "how can we force our query to use the value of dname as an XML tag?"

And, of course, Oracle have given us a solution - the evalname keyword will tell the query that the expression following it is to be evaluated and that the result of that evaluation should be used as the XML tag.  Armed with this knowledge, we'll now make a small change to the query

 select xmlelement("departments",
           xmlagg(
              xmlelement(evalname lower(dname), 
                 xmlagg(xmlelement("employee", initcap(ename))))))
   from dept d
   left outer join emp e on (e.deptno = d.deptno)
  group by d.deptno, d.dname;
Result:
<departments>
 <accounting>
  <employee>King</employee>
  <employee>Miller</employee>
  <employee>Clark</employee>
 </accounting>
 <research>
  <employee>Jones</employee>
  <employee>Adams</employee>
  <employee>Smith</employee>
  <employee>Ford</employee>
  <employee>Scott</employee>
 </research>
 <sales>
  <employee>Blake</employee>
  <employee>James</employee>
  <employee>Turner</employee>
  <employee>Martin</employee>
  <employee>Ward</employee>
  <employee>Allen</employee>
 </sales>
 <operations>
  <employee></employee>
 </operations>
</departments>

So, as we can see, the value of the expression following the evalname keyword has been used as the XML tag.

Of course, the above example is a simple example to illustrate the use of evalname.

The actual problem that I was solving involved calculating multiple tag values according to a reasonably complex piece of business logic that was implemented via PL/SQL packages.  These tag values were then passed as parameters to the procedure generating the XML.  From there it was easy to just use evalname parameter_name for the dynamic tag names.

The above was run and tested on the Oracle Autonomous Database Cloud using an "Always Free" database that is running database version 19c at the time of writing.

I hope that this is useful for some of you.  Happy XMLing with SQL and PL/SQL!