Using XML Schemas (XSDs) to Create Database Tables

Introduction

Developers often need to change the type of data source used for Web projects. Deciding to change from XML to a Relational database system is a common task, and means you can base your Websites and applications on a system that is robust, reliable and easy to connect to using Server Side scripting.

Creating your database tables from XML content or XSD Schema Definitions is generally straightforward. In most cases the XML tree structure can be translated into a Relational database structure, with all of the data elements and relationships represented accurately.

Model

Create a model for your database structure by referring to the elements in your XML and XSD. There are many ways to model database structure, but the most common is to simply list your tables on paper. Coming up with a good design before you build your database is essential for efficiency. Look at your XML and XSD Schema, and list all of the elements that have child elements, as the element "person" does in this example:

John Smith
Postman

It is not necessarily the case that these elements will become tables in your database, but this is the starting point for you to figure that out.

Translate

Translate each element of data in your XML into either a table, or a column in a table, by listing each of them in turn. Starting with your list of elements that have child elements, create a draft list of tables.

Initially assume that the elements you listed are all going to become tables, and list their child elements (for example "name" in the "person" element) as columns. List the name of each column and decide on a data type for it, which may be indicated in your XSD as in the following example, where "occupation" is listed as being a text string:

Structure

Check the structure of your tables by making sure every item of data in your XML can be contained. Also check that no items of data have been repeated, as this will result in an unreliable, inefficient data structure. If your data contains any information that you have not listed among your tables and columns, extend your list to include it, considering whether it makes more sense as a new table, or a column within an existing table.

Columns in existing tables are for items that provide additional information about another item, rather than existing as a core item of data in themselves. In the "person" example, "name" and "occupation" would be columns in the "person" table.

Relationships

Look at your list of tables and identify any relationships between them, for example between tables that were child and parent elements in the original XML. If the "person" element was a child of the following example "country" element, "country" would have a table of its own in the database as well:

Canada

John Smith
Postman


Jane Doe
Teacher

The "country" and "person" tables have a "one to many" relationship, meaning that one "country" item can be associated with many "person" items. To reflect this relationship, you need to add a "Foreign Key" column to the list for your "person" table indicating which "country" the "person" is associated with, which you will do when you create your SQL.

Implement

Create your database tables using your chosen Relational Database Management System, either through a Web interface provided by your Web host, or by creating SQL statements according to the following syntax:

CREATE TABLE Country
( Country_ID int, Country_name varchar(50) )

Each table you create should have a column listing an ID as in the above example, and this ID will be included as a "Foreign Key" linking tables as follows:

CREATE TABLE Person
( Person_ID int, Name varchar(50),
Occupation varchar (20),
Country_ref int )

Each record in the "Person" table will include the "Country_ID" listed in the relevant "Country" record, in its "Country_ref" column, creating a relationship between the two. Enter data from your XML into your new database to make sure it can accommodate everything you need.

Notes

• If you find data items in your XML content that do not have a logical home in your database, you may need to make additions to your database design, or to amend it in some way.
• It is not simply the case that elements in XML should be represented as tables in a database, and that their attributes become columns, as this will not always result in the most effective design. Make sure you think carefully about the elements and attributes in your data, and optimize them to suit Relational database structures.

Links

• Fundamentals of Relational Database Design
• XML Schema Simple Elements
• XML Schema Complex Elements
• MySQL 5: Creating a Table
• XML Database Mapping Using XSD (XML Schema) | The Tech Interpreter

Using XML to Control LCD Output in XBMC

About XBMC

XML can be used to control LCD display options within the open source media player XBMC (initially known as Xbox Media Center), which was originally used within Xbox gaming consoles. XBMC is now available as a general media player on many different types of device, including both TVs and computers, and can be run on most of the major operating systems.

XBMC can be used to watch TV on computer, as well as listen to the radio and view Electronic Program Guides (EPGs). Aside from the main version of XBMC, there are also many derivative programs including Boxee, iConsole, MediaPortal, Voddler and Plex.

Controlling LCD Output

Using XML to control the LCD output within an XBMC system requires editing a file named "LCD.xml" to reflect the settings you want enforced. Once you have the file located, editing it is not typically difficult, but you do need to work out what settings you want. The easiest way to find what XML LCD settings are possible is to refer to the list in the official XBMC Wiki Manual.

Find the LCD XML file

Locate the LCD XML file for your XBMC installation. The file should be located within your "UserData" folder. Where the "UserData" folder is depends on which operating system you are using.

If you're using Windows XP, look in "Documents and Settings," your username, "Application Data," "XBMC." For Windows 7 and Vista, look in "Users," your username, "AppData," "Roaming," "XBMC."

If you're using a Mac, look in "Users," your username, "Library," "Application Support," "XBMC," "userdata."

If you're using Linux, look in your home folder, ".xbmc," "userdata." If you can't find the file, try searching your entire system for "LCD.xml" or "lcd.xml."

Have a look around

Open the file and look at the contents in a text editor. If you are unfamiliar with the XML format you may need to acquaint yourself with the syntax. It helps if you have a text editor that can highlight XML code (such as Notepad++ or Gedit) or even an XML viewer, many of which are free to download.

XML files use a tree structure, with tags enclosing sections of data as in this example:

33
Jim

Don't worry if you are not accustomed to reading code, XML is an easy markup language to work with. Save a copy of your LCD XML file somewhere else on your computer before making any changes to it.

Find what you're looking for

Find the part of your LCD XML file containing the section you want to change. There are five main sections you can make changes to: Navigation, Music, Video, General and Xbelaunch (for displaying game and application labels). Locate the section relevant to the display property you want to change, for example the display during music playback, which is surrounded by "<music>" tags, and contains "<line>" elements.

Make Adjustments

Make adjustments to the appropriate function sections of your LCD XML file according to what settings you want the display to reflect. To do this, you need to include "InfoLabel" variables, examples of which you should see within your existing file. These labels will cause the LCD display to output certain types of information about whatever media is playing. For example, you can display the title of the current song using the following syntax within the "<music>" section:

$INFO[MusicPlayer.Title]

Browse through the list of available labels on the XBMC Online Manual and choose from these to suit your display preferences.

Additional options

Choose special labels to suit your preferred display options. As well as choosing from the standard list, you can choose additional settings such as turning the LCD display off when playback of music is ongoing:

music

Additionally, there are labels specific to the LCD output for various settings, such as displaying a progress bar for the media item that is currently playing:

LCD.ProgressBar

Notes

• In some cases you may need to rename the LCD XML file to "lcd.xml" changing from upper to lower case for your changes to take effect.
• Changing the LCD XML settings is really only for advanced users, so be careful and make sure you keep a copy of the file in its original state.

Links

• XBMC Online Manual
• XML Tutorial
• Customising LCD Output in XBMC | The Tech Interpreter