In this post we will convert the TransXChange XML standard to a relational format on MariaDB. We will query the output with SQL to show the bus schedule (pick up and drop down routes and times). We will use the relational database management system Maria DB and Sonra’s data warehouse automation tool for XML, JSON, and industry data standards Flexter. Flexter can quickly convert TransXChange XML in a few simple steps.

TransXChange

TransXChange is the UK nationwide standard for exchanging bus schedules and related data. It is used both for the electronic registration of bus routes (EBSR) with Vehicle and Operator Services Agency (VOSA) and the Traffic Area Networks (TAN), and for the exchange of bus routes with other computer systems, such as journey planners and vehicle real-time tracking systems.
The format is a UK national de facto standard sponsored by the UK Department of Transport. The standard is part of a family of coherent Transport related XML standards that follow GovTalk guidelines.

MariaDB

MariaDB is a open-source, commercially supported fork of the MySQL relational database management system (RDBMS). MariaDB is based on SQL and supports ACID-style data processing with guaranteed atomicity, consistency, isolation and durability for transactions.
MariaDB intends to have high compatibility with MySQL and exact matching with MySQL APIs and commands. MariaDB’s API and protocol are compatible with those used by MySQL, and also some other features to support local non-blocking operations and progress reporting.

Processing XML with Flexter

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to Maria DB can be performed in a few simple steps.
Step 1 – Authenticate
Step 2 – Define Source Connection (Upload or S3) for Source Data (JSON/XML)
Step 3 – Optionally define Source Connection (Upload or S3) for Source Schema (XSD)
Step 4 – Define your Target Connection, e.g. Snowflake, Redshift, SQL Server, Oracle etc.
Step 5 – Convert your XML/JSON from Source to Target Connection

Step 1 – Authenticate

To get an access_token you need to make a call to /oauth/token with Authorization header and 3 form parameters:

• username=YOUR_EMAIL
• password=YOUR_PASSWORD
• grant_type=password

You will get your username and password from Sonra when you sign up for the service.

Example of output

Step 2 – Define Source Connection (Upload) for Source Data (TransXChange XML)

In a second step we will upload our TransXChange XML source data

Example of output

Step 3 – Optionally define Source Connection (Upload or S3) for Source Schema (XSD)

In next step we will define Source Schema by uploading TransXChange XSD files.

Example of output

Step 4 – Define Target Connection (Maria DB)

In the next step we will define the Target Connection. In this example we convert our XML data to a relational format in Maria DB.
We give the Target Connection a name and supply various connection parameters to the Maria DB database. We use same connection parameters as we do for MySQL.

Example of output

Step 5 – Convert XML data from Source Connection (Upload) to Target Connection (Maria DB)

In the last step we convert TransXChange XML. Data will be written to the Maria DB Target Connection.

Example of output

Downloading ER Diagram

We can create and download an ER Diagram of the model that Flexter generated by making a GET call.

Example of output

You can download the ER Diagram here.

Querying tables on Maria DB

After data is converted to Maria DB we will use DBeaver to query it. As a result we will get which Routes and bus stops are pick up or pick up and sit down.

Conclusion

We have shown you how to find your bus stop by converting data locked away in the TransXChange XML standard to Maria DB. With Flexter it’s a simple and fast process.
Our enterprise edition can be installed on a single node or for very large volumes of XML on a cluster of servers.
If you have any questions please refer to the Flexter FAQ section. You can also request a demo of Flexter or reach out to us directly.