XML-Generation in SQL with Namespaces
Tags: 100codeexamples, sql, oracle, xml
Published: 2023-02-24 14:56:36

---
title: XML-Generation in SQL with Namespaces
published: true
date: 2023-02-03 16:45:25 UTC
tags: 100CodeExamples,SQL,Oracle,XML
canonical_url: https://developer-sam.de/2023/02/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` :

```sql
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:

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

| 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:

```sql
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
``` 

```xml
<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:

```sql
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:

```sql
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:

```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!

The post [XML-Generation in SQL with Namespaces](https://developer-sam.de/2023/02/xml-generation-in-sql-with-namespaces/) appeared first on [Developer Sam](https://developer-sam.de).