The Tech Interpreter: xml schema

Thursday, 30 August 2012

XML Database Mapping Using XSD (XML Schema)

It is not uncommon for developers working on software or Web applications to change data storage and modelling approaches. An example of this would be deciding to switch from XML data to a relational database. The decision to shift data modelling technologies may be a result of changes in the aims or other technologies being used within a project.

Changing from XML to a relational database system is not typically a difficult task. The main activity involved is mapping the data structures from one system to the other. The data structure includes the data items and the relationships between them. Ultimately the database will be defined using SQL but the initial stage involves creating a design, an abstract model of the database determining the structures its data content will be held in.

If an XML data source is accompanied by an XML Schema Definition (XSD) determining its structure, this can be used as the primary source for mapping the data to a relational system. XML Schemas define the structures and elements an XML data source can contain, including the names and datatypes of elements, their attributes and the relationships between them.

Techniques For Mapping

There is no set procedure for mapping XML to a relational structure, although there are some common approaches. The process should be accompanied by an informed sense of the system for which the data is being modelled, with the overall purpose of this system in mind when mapping decisions are being made.

Element To Table

Often the most logical step will be to translate XML elements into relational database tables. This is not always the best approach, it does depend on the data in question. If an XML element is mapped into a database table, its columns may become the element attributes, optionally the element content or children.

Element To Column

In some cases XML elements may become columns in other database tables. This is most likely the case with simple elements, which simply contain a text string, easily translatable into a column in a system such as MySQL. If an element contains further elements or attributes, the possibility of mapping only into a column will be less likely.

Attribute To Column

Since attributes are single, simple pieces of data, it will generally make most sense to translate them into columns in the database tables corresponding to their given elements. An alternative may be in cases where there are only a set, finite number of possible attribute values, in which case this may be reflected in different tables for the elements having each attribute type.

For example, if an element called "thing" has an attribute "type" and this attribute can only be "red" or "green" it may be reflected by having two tables, one for "red things" and one for "green things". However, in most cases it will be more sensible to have a "thing" table with a "type" column in it.

Relationships

Representing relationships in a data set is one of the more challenging aspects of mapping from XML to relational database using an XSD. Relationships in XML are implemented by structure, for example with an element having child elements. In relational database systems such relationships are reflected using "keys" linking different tables.

For example, consider the following XML structure:


Mary
Jim
Tony

The corresponding XSD excerpt:

This could be reflected in a relational database using Foreign Keys. The relationship is "one to many" in that one family may be associated with multiple members, but each member will typically only be part of a single family. In this case, a relational database could contain a "family" table with a "name" column and a unique "ID" column representing the table's Primary Key. An additional "member" table could include a "name" column (containing "Mary", "Jim" or "Tony" in this case). The "member" table could use a Foreign Key by including a column called "familyID" in which the "ID" for the member's family is recorded, linking the two tables in a way that accurately represents their relationship.

Finally

Ultimately, there is no one correct way to translate XML indicated by an XSD into a relational database design. An understanding of both technologies, together with information about the project the data is being used for, will make a successful outcome far more likely.

See also:

Friday, 29 June 2012

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.