How to generate XML from relational data – with line breaks!

August 24, 2011

Generating XML from relational data is not difficult. The SQL standard contains a set of XML construction functions that take column names or other SQL expressions as input and return XML. The most prominent of these XML construction functions are:

  • XMLELEMENT (constructs an XML element)
  • XMLATTRIBUTES (constructs one or more XML attributes)
  • XMLFOREST (constructs a sequence of XML elements)
  • XMLTEXT (constructs an XML text node)
  • XMLAGG (aggregates data from multiple rows into a single XML fragment)
  • XMLNAMESPACES (constructs a namespace declaration)
  • etc.

Here is a simple example:

create table mydata(c1 int, c2 varchar(25));
insert into mydata values (1, ‘This is a test’);

select xmlelement(name “myroot”,
               xmlelement(name “col1″, c1),
               xmlelement(name “col2″, c2) )
from mydata;

<myroot><col1>1</col1><col2>This is a test</col2></myroot>

 1 record(s) selected.

The XML is typically returned in textual (serialized) format, as a single line without any line breaks. That’s perfectly fine, except that line breaks can sometimes be useful to make the XML easier to read for the human eye.

There are several ways in which you add add newline characters to the XML to make it more readable.

If the XML that you construct is less then 32KB in size, then you can use the SQL function REPLACE to replace every occurrence of ‘><‘ with the hex string 3E0A3C. The hex character 3E is the same as ‘>’, the hex character 3C is the same as ‘<‘, and the hex character 0A is a line feed in between them. Before we can apply the REPLACE function to the constructed XML, we need to explicitly convert the  XML to data type VARCHAR, which can be done with the function XMLSERIALIZE.

The following query is extended from the previous example by adding the functions XMLSERIALIZE and REPLACE:

select replace(
       xmlserialize(
          xmlelement(name “myroot”,
               xmlelement(name “col1″, c1),
               xmlelement(name “col2″, c2) ) AS VARCHAR(32000)),
         ‘><‘,
         x’3E0A3C’)       
from mydata;

<myroot>
<col1>1</col1>
<col2>This is a test</col2>
</myroot>

 1 record(s) selected.

The replace function takes three parameters:

  • the constructed serialized XML
  • the string that should be searched and replaced, i.e. ‘><‘
  • the new string that replaces the search string, i.e. x’3E0A3c’

However, this approach works only for smallish XML, up to 32KB.

If you generate larger XML, you have two options. First, you can perform the replace operation at the client that receives the XML. Second, you can explicitly construct XML text nodes that contain newline characters where you want them. For example:

select xmlelement(name “myroot”,
              xmltext(x’0A’),         
              xmlelement(name “col1″, c1),
              xmlelement(name “col2″, c2),
              xmltext(x’0A’) )
from mydata;

<myroot>
<col1>1</col1><col2>This is a test</col2>
</myroot>

 1 record(s) selected.

 

select xmlelement(name “myroot”,
              xmltext(x’0A’),         
              xmlelement(name “col1″, c1),
              xmltext(x’0A’),
              xmlelement(name “col2″, c2),
              xmltext(x’0A’) )
from mydata;

<myroot>
<col1>1</col1>
<col2>This is a test</col2>
</myroot>

 1 record(s) selected.

If you want a line break after every XML element then this approach can be tedious and makes the SQL statement quite a bit longer. Then again, the line breaks are really only needed for human readability, not for any client application that consumes the generated XML.

 

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 52 other followers

%d bloggers like this: