Generating XML from SQL and PL/SQL – Part 1: Introduction

From time to time you’ll have to represent your data stored in a database in an XML format, eg. to exchange it between systems, to send it to external parties, etc. In this introduction, I’ll show you step by step using small examples how you can easily generate XML from tables. SQL There are some SQL functions available to generate

From time to time you’ll have to represent your data stored in a database in an XML format, eg. to exchange it between systems, to send it to external parties, etc. In this introduction, I’ll show you step by step using small examples how you can easily generate XML from tables.

SQL

There are some SQL functions available to generate XML. The first function is XMLElement, it creates an element for the data specified, usually coming from a table column:

Resulting in these two rows(from two records):

That’s the easy part, generating data surrounded with XML tags. But of course you need to add attributes to these elements. This is still easy to do with the XMLAttributes function:

OK…We now can create an XML Element with attributes, but we have more data than one element. So we add another element:

Result:

Oops…two columns, not really the way I wanted it, I want all XML in 1 column. We can use XMLForest for this.  It generates a forest of XML Elements, i.e. multiple XML Elements:

That’s more like it, one column for each record. And by combining XML Element and XML Forest we can get this:

An XML Element containing an XML Forest, i.e. one element containing multiple elements.

Up to now, we got multiple rows back from the query. Using XMLAgg we can retrieve one row with all records from the query:

These are some SQL functions to generate XML and when combining these functions, one can create some nice and more complex XML Documents:

This query generates two rows, one row for each department:

  • The “Department” element has two attributes (ID and name) and contains an “Employees” element
  • The “Employees” element consists of multiple rows (all employees for that department)
  • Every row of the “Employees” element has an “Employee” element (single employee record)
  • Each “Employee” element has multiple elements: “ID” and “Name”

It looks like this:

We can format this to make it clearer:

I hope this will be a good start to create XML documents from the data in your tables. In part two I will show you how to create XML from PL/SQL.

Oracle documentation on Generating XML using SQL Functions.