XML-Generation in SQL with Namespaces

One of my favorite features of (Oracle-)SQL is how easy it is to turn relational data into other data formats like JSON or XML.

Let’s assume we have some data in the tables character and favorite_food :

create table characters (
  id integer not null primary key,
  name varchar2(100) unique
);

create table favorite_food (
  character_id integer not null references characters( id ),
  food varchar2(4000)
);

insert into characters values (1, 'Chewbacca');
insert into characters values (2, 'Darth Vader');

insert into favorite_food values ( 1, 'Grilled Pork');
insert into favorite_food values ( 1, 'Cooked Pork');
insert into favorite_food values ( 1, 'Raw Pork');
insert into favorite_food values ( 2, 'Cheesecake');

Selecting will lead to a table like this:

select *
from characters c
  inner join favorite_food ff on c.id = ff.character_id;

With SQL, it is very easy to turn this into a nested XML structure:

select
    xmlelement("Characters",
      xmlagg(
        xmlelement("Character",
          xmlforest(
            c.name as "name"
          ),
          xmlelement("favouriteFoods",
            xmlagg(
              xmlforest(
                ff.food as "food"
              )
            )
          )
        )
      )
    )
from characters c
  inner join favorite_food ff on c.id = ff.character_id
group by name

<Characters>
    <Character>
        <name>Chewbacca</name>
        <favouriteFoods>
            <food>Grilled Pork</food>
            <food>Raw Pork</food>
            <food>Cooked Pork</food>
        </favouriteFoods>
    </Character>
    <Character>
        <name>Darth Vader</name>
        <favouriteFoods>
            <food>Cheesecake</food>
        </favouriteFoods>
    </Character>
</Characters>

But what if we need to add XML Namespaces?

XML is a very powerful and therefore sometimes a bit complex format. For example, it comes with namespaces to allow different structures to build on each other without name conflicts.

What if we require all the nodes to start with our very own http://developer-sam.de/codeexamples namespace?

It’s relatively easy to get a prefix into a node name, since xmlelement allows anything in the name element:

select
    xmlelement("sam:Characters",
...

But how do we get that xmlns:sam="http://developer-sam.de/codeexamples" into our main node?

Maybe that’s easy to answer for you, but I needed a while of thinking and trying out today before it hit me:

The xmlns in an XML is just an ordinary XML attribute.

And therefore we can easily add it:

select
    xmlelement("sam:Characters",
      xmlattributes(
        'http://developer-sam.de/codeexamples' as "xmlns:sam"
      ),
      xmlagg(
        xmlelement("sam:Character",
          xmlforest(
            c.name as "name"
          ),
          xmlelement("favouriteFoods",
            xmlagg(
              xmlforest(
                ff.food as "food"
              )
            )
          )
        )
      )
    )
from characters c
  inner join favorite_food ff on c.id = ff.character_id
group by name

And Oracle even puts in the xmlns wherever it is used later in the XML:

<sam:Characters xmlns:sam="http://developer-sam.de/codeexamples">
    <sam:Character xmlns:sam="http://developer-sam.de/codeexamples">
        <name>Chewbacca</name>
        <favouriteFoods>
            <food>Grilled Pork</food>
            <food>Raw Pork</food>
            <food>Cooked Pork</food>
        </favouriteFoods>
    </sam:Character>
    <sam:Character xmlns:sam="http://developer-sam.de/codeexamples">
        <name>Darth Vader</name>
        <favouriteFoods>
            <food>Cheesecake</food>
        </favouriteFoods>
    </sam:Character>
</sam:Characters>

I find this very neat!