Click here to Skip to main content
Click here to Skip to main content

Choosing between XML and SQL in .NET Content Management Systems

This article compares various approaches to storing, editing, and displaying structured content on the web. It shows the advantages and disadvantages of using XML and relational SQL databases.

Editorial Note

This article is in the Product Showcase section for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.

This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.

Introduction

This article compares various approaches to storing, editing, and displaying structured content on the web. It shows the advantages and disadvantages of using XML and relational SQL databases.

Choosing the right content storage is the crucial decision of every dynamic web site. How to store content? How to edit it? How to transform it? This article discusses various approaches based on XML and relational SQL databases.

Choosing the Data Storage

The basic question is obviously: Where do I store the data? You can choose between:

  • XML files stored on the disk,
  • an XML database,
  • some proprietary storage, or
  • a relational SQL database.

XML files stored on the disk can be used for very basic web sites since they do not provide any data management features, such as concurrent access, indexing, security management, etc.

While XML (or object-oriented) databases can be an optimal solution in some specific cases, their lack of standards, higher cost of ownership, and limited adoption on the market will most likely let you choose a different solution. You will also most likely choose not to use a proprietary storage since you want to build a web site, not your own database system.

Traditional (relational) SQL databases seem to be a reasonable choice – they’re affordable and wide-spread. They are also optimized for high performance. It makes them the right solution for a general-purpose CMS.

Storing Structured Content

Now that we have chosen an SQL database for storing data and other system information, such as users and permissions, we need to choose how to store our content in the database. There are two basic approaches that you can see in the current content management systems:

  • Storing whole documents as XML in a single field.
  • Storing documents of different types in separate tables.

Storing Documents as XML

This approach assumes that you store all documents in a single database table that has a single field for holding the whole document. It may look like this:

Storing documents as XML in a single table

The DocumentXML field contains XML documents like this one:

<article>
  <title>My first article<title>
  <summary>This is a summary.</summary>
  <articletext>This is full article text.</articletext>
  <teaserimage>/articles/myfirst.gif</teaserimage>
</article>

Storing Documents in Separate Tables

If you are accustomed to standard relational database schemas, you will most likely want to have a separate database table for every document type. So you will have database tables like these:

Separate tables for every document type.

And you may want to join the common fields into one Documents table while keeping document type-specific fields in separate tables:

Separate tables for every document type and one table with common fields.

Which One is Better?

Both of these approaches have their pros and cons. The XML approach is generally easier to implement and more flexible to changes. It is suitable for semi-structured content, such as a page with several text-only sections:

The editable text regions of the page can

You will benefit from this option if you need to edit semi-structured, text-oriented content while keeping it separated from the page design. You can simply change the presentation by modifying the page template design at any time.

However, you may encounter severe problems when you need to display more than one XML document on the page. Just consider a product catalog with dozens of items: when you need to display a listing of products, you need to go through all database records, retrieve the XML document, parse it, and render. As you can imagine, this is not straightforward, and the performance will not be optimal.

Besides, the XML approach doesn’t really support typed data (such as integer, datetime, etc.) as all content is serialized to text. If you decide to sort products by price using XSLT, you will sort them as text which will result in incorrect sorting. You will also need to ensure that date-time values or decimal numbers are parsed and displayed in the correct format.

In the case of strictly structured data, such as product specifications, you will benefit from the traditional relational database approach. It allows you to easily retrieve data from the database and display them on the web site using standard ASP.NET controls, such as DataGrid, while having a complete control over the format. You can also easily sort them or filter them on the database server, while leveraging the indexes that provide optimal performance. However, this approach results in several challenges that we discuss in the following paragraphs.

Facing the SQL Challenge

The traditional SQL approach is (by design) less flexible for content management. The database structure is not so flexible to changes. Look at this table:

Simple job table

Let’s consider that your HR manager wants to publish an annual salary for this job opening. What you typically have to do is:

  • add a table column JobSalary,
  • modify the SQL queries,
  • modify the code you use for editing the content and add the salary field to the editing form, and
  • modify the code you use for displaying job descriptions.

The solution for this issue is to add a "meta" layer to your code. Instead of writing all code by hand or generating code, you may want to create a system where you describe the data structure or editing form and let your content management engine process it.

Here’s what we have done in Kentico CMS for .NET: When you want to add a new field to the document type, you simply use the web-based user interface and add a new attribute to the document of type “Job”. You also specify that this attribute is of decimal type, and that it should be displayed as a textbox in the editing form:

Document fields editor in Kentico CMS .NET

The system automatically updates the related database table:

Kentico CMS .NET automatically manages the database structure

It also updates the standard SQL queries for INSERT, UPDATE, SELECT, and DELETE:

Kentico CMS .NET automatically generates the SQL queries

When you edit the job document now, you will see a dynamically rendered editing form like this one:

Kentico CMS .NET automatically renders the editing form

The only hand coding you need to do is to add an appropriate field to the transformation that represents the ItemTemplate section of the ASP.NET Repeater or DataList:

You only need to edit the transformation in Kentico CMS .NET

The document on the web looks like this:

Jobs displayed on the web site running on Kentico CMS .NET

How it Works

You may wonder how this works inside: All document types (news, jobs, product specifications) are described using configuration files that define the:

  • data schema,
  • standard SQL queries,
  • editing form, and
  • transformations.

The following figure shows how these elements work together:

.NET CMS architecture based on meta layer

The CMS engine receives a request for a page. It reads the settings (SQL queries, etc.) for the given document type. It runs an SQL query to select data from the database. Then, it binds the retrieved DataSet to the Repeater control (or some other control) that displays the content using a predefined transformation. The page is displayed to the visitor.

Conclusion

As you can see, there’s a place for both XML and SQL-oriented approach in the content management world. Both of them have their pros and cons, and you need to choose them depending on the particular situation.

Now, you can download the free trial of Kentico CMS for .NET and try it for yourself. The Kentico CMS Tutorial will guide you through the process of creating web pages with both XML-oriented and SQL-oriented design.

Quick Links

Kentico CMS for .NET Fast Facts

Key content management features
  • Tree organization of documents
  • Content/design separation
  • Workflow
  • Versioning
  • Content scheduling
  • Multilingual content
  • Document-level permissions
  • Content Staging (synchronization between servers)
Key web site features
  • Flexible navigation using several types of navigation controls
  • Full-text search in both content and files
  • Secured areas for registered users
  • Multilingual content
Built-in modules
  • E-commerce/Shopping cart
  • On-line forms
  • Forums
  • Newsletter
  • Image gallery
Supported platforms
  • ASP.NET 1.1
  • ASP.NET 2.0
Supported IDEs
  • Visual Studio .NET 2003
  • Visual Studio 2005
  • Visual Web Developer 2005 Express Edition
Supported programming languages
  • C#
  • Visual Basic .NET
Supported databases
  • Microsoft SQL Server 2000
  • MSDE 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2005 Express
Supported user interface languages
  • English
  • Italian
  • German
  • Spanish
  • Dutch
  • Czech
  • Slovak
  • (you can easily create your own translation)
Supported content languages
  • Any language – Kentico CMS uses UNICODE encoding for all content.
Supported web browsers for editors
  • IE 6.0+
  • Mozilla 1.7.1+
  • Netscape 7.1+
  • FireFox 1.3+
Supported web browsers for visitors
  • Depends mostly on your HTML code, transformations, and used controls.

Other Kentico Products

You may also want to try the following Kentico products for developers:

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Petr Palas
Web Developer
Czech Republic Czech Republic
Petr Palas is founder of Kentico Software (www.kentico.com), the producer of professional solutions for web developers. Kentico Software is focused on development and marketing of Kentico CMS for ASP.NET. Our goal is to create the most flexible and easy-to-use web content management solution for ASP.NET developers.

Comments and Discussions

 
GeneralFalse statements in your article Pinmemberkckn4fun9-May-06 16:41 
GeneralRe: False statements in your article PinmemberPetr Palas9-May-06 21:44 
GeneralRe: False statements in your article Pinmemberbroadbent24-May-06 0:19 
GeneralRe: False statements in your article PinmemberPetr Palas24-May-06 0:57 
GeneralNice PinmemberNaga Rajendra Kumar9-May-06 2:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 2 May 2006
Article Copyright 2006 by Petr Palas
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid