Db2: Some Friday Fun with XML and SQL recursion

Recursion is fun!?

Right now, Michael Tiefenbacher  and I have to prepare our joint talk "Some iterations over recursion" for the IDUG Db2 Tech Conference in Malta 2018. The title of our talk promises some fun, coding up some of the sample SQL already is. Yesterday and this morning I tested a little query I wrote and brought down my machine (not Db2). In April, I wrote about two different kind of recursive queries, standard SQL and Oracle syntax. Today, let me give you insights to a nasty recursive query that features SQL, SQL/XML and XQuery.
The following query constructs a nested XML document. It utilizes XMLELEMENT to add XQuery element nodes to the document. Some of those elements are composed of text ("Hello recursive world"), others are more fancy. They are XML fragments generated by XMLQUERY with help of a FLWOR expression. The properties of the generated XML document are defined by its depth and fan out. Both parameters can be set within the query and are shown with a value of 5 and 10.

with mytable(mydoc,depth,fanout,level) as
 (select xmlelement(name "first",'Hello recursive world'),
  5,10, 1 as level
  from sysibm.sysdummy1
union all
  select xmlelement(name "in-between",
     xmlquery('<a>{for $i in (1 to $FANOUT)
      return <b>{$MYDOC}</b>}</a>')), 
     depth,fanout, level+1 as level
  from mytable where level<depth)
select xmlelement(name "root",mydoc) as doc
from mytable
where level=depth


So what is the result of that query and what does the color coding mean? Attend the IDUG conference and our session C10... ;-) Or run the query on your own.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.