DEV Community

Samuel Nitsche
Samuel Nitsche

Posted on • Originally published at developer-sam.de on

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');
Enter fullscreen mode Exit fullscreen mode

Selecting will lead to a table like this:

select *
from characters c
  inner join favorite_food ff on c.id = ff.character_id;
Enter fullscreen mode Exit fullscreen mode
ID NAME CHARACTER_ID FOOD
1 Chewbacca 1 Grilled Porg
1 Chewbacca 1 Cooked Porg
1 Chewbacca 1 Raw Porg
2 Darth Vader 2 Cheesecake

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
Enter fullscreen mode Exit fullscreen mode
<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>
Enter fullscreen mode Exit fullscreen mode

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",
...
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

I find this very neat!

The post XML-Generation in SQL with Namespaces appeared first on Developer Sam.

Top comments (0)