Project Advice

Project Advice

2. Reusing old data

2.1 Database tables

Database tables, by definition, consist of structured data, so normally provide good base data for creating XML. MySQL clients such as PHPMyAdmin, and Microsoft’s Access provide an export to XML option:

Access 2007 export XML dialogue box

The usual procedure is that column names become children of the table name, for example a table called Russian_tsars will export from:

 

ID

Tsar Name

Reigned From

Reigned To

1

Alexander II

1855

1881

2

Alexander III

1881

1894

3

Nicholas II

1894

1917

 

to something like this:

<?xml version="1.0" encoding="UTF-8"?>

<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="Russian%20Tsars.xsd" generated="2012-04-23T13:43:26">

<Russian_x0020_Tsars>

<ID>1</ID>

<Tsar_x0020_Name>Alexander II</Tsar_x0020_Name>

<Reigned_x0020_From>1855</Reigned_x0020_From>

<Reigned_x0020_To>1881</Reigned_x0020_To>

</Russian_x0020_Tsars>

<Russian_x0020_Tsars>

<ID>2</ID>

<Tsar_x0020_Name>Alexander III</Tsar_x0020_Name>

<Reigned_x0020_From>1881</Reigned_x0020_From>

<Reigned_x0020_To>1894</Reigned_x0020_To>

</Russian_x0020_Tsars>

<Russian_x0020_Tsars>

<ID>3</ID>

<Tsar_x0020_Name>Nicholas II</Tsar_x0020_Name>

<Reigned_x0020_From>1894</Reigned_x0020_From>

<Reigned_x0020_To>1917</Reigned_x0020_To>

</Russian_x0020_Tsars>

</dataroot>

As you will have seen from the screenshot (from Access 2007, in this case) you may be offered other useful documents, such as a Schema file and an XSL file.

The Russian Tsars example above is a single database table that contains all we need. However relational databases are split into different tables and it may be that you need your XML to take data from multiple tables. The way to do this is to carry out joins. Joins are a key technique in database manipulation, allowing you pull in the required information from various tables. One-to-one joins (where there is one x for every one y that you wish to put together) are simple, but more complex joins are sometimes necessary and, if you’re not a regular database user, can have unforeseen consequences.  If you need to do complex joins, be sure to double-check that they have worked as planned before exporting your XML.