Serve SQL Data in XML Format

• By Ivan Pepelnjak
• Sep 28, 2007

Like this article? We recommend
Ajax Construction Kit: Building Plug-and-Play Ajax Applications

If you’re developing AJAX applications, you might decide to use XML as the encoding method to transport application data between the web server and the browser—not surprisingly, as XML is the technology that contributed the X in AJAX. Quite often, the data you have to serve to the client will reside in SQL databases on the server, so you need an adapter between the tabular binary SQL data and text-oriented hierarchical XML data. As you’ll see in this article, you can implement this adapter in a number of ways, depending on the SQL database you use and the implementation flexibility you need.

Before we get into the implementation details, let’s review the XML representation possibilities.

XML Representation Options

You can represent an SQL row as an XML tag with an attribute for each column value; or as an XML tag with child tags, one for each column. Listing 1 shows how a record from a Categories table is represented as a tag with attributes. Listing 2 shows the tag with the child tags format.

Listing 1 SQL row as XML attributes.

<Category CategoryId="HTML" CategoryName="HTML (Hypertext Markup Language)" />

Listing 2 SQL row as XML child tags.

<Category>
 <CategoryId>HTML</CategoryId>
 <CategoryName>HTML (Hypertext Markup Language)</CategoryName>
</Category>

Obviously, you can also mix the two; some columns could be represented as attributes and others as child tags (see Listing 3). Furthermore, you might want to use shorter names for XML tags and attributes, to cut down on response size and bandwidth utilization (see Listing 4).

Listing 3 Mixed XML format.

<Category CategoryId="HTML">
 <CategoryName>HTML (Hypertext Markup Language)</CategoryName>
</Category>

Listing 4 Optimized XML output.

<cat id="HTML">
 <name>HTML (Hypertext Markup Language)</name>
</cat>

Choosing the XML format is primarily a matter of convenience if you’re simply dumping SQL tables into XML. (The attribute notation results in slightly shorter XML code.) However, if you want to implement more complex structures, you have to consider the following issues:

• If your XML data contains HTML or XML fragments, you might want to represent them as child tags to simplify the client-side processing. In this case, at least the columns with HTML or XML fragments should be represented as child tags.
• If your XML response represents hierarchical data (for example, invoice with a header and a variable number of lines), you should use the hierarchical XML structure, not the flattened tabular version produced by an SQL join.