Converting and Querying TransXChange XML to a database (Maria DB)
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.
Table of Contents
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.
|
1 2 3 4 |
curl --location --request POST "https://api.sonra.io/oauth/token" \ --header "Content-Type: application/x-www-form-urlencoded" \ --header "Authorization: Basic NmdORDZ0MnRwMldmazVzSk5BWWZxdVdRZXRhdWtoYWI6ZzlROFdRYm5Ic3BWUVdCYzVtZ1ZHQ0JYWjhRS1c1dUg=" \ --data "username=XXXXXXXXX&password=XXXXXXXXX&grant_type=password" |
Example of output
|
1 2 3 4 5 6 7 8 |
{ "access_token": "eyJhbG........", "token_type": "bearer", "refresh_token": "..........", "expires_in": 43199, "scope": "read write", "jti": "9f75f5ad-ba38-4baf-843a-849918427954" } |
Step 2 – Define Source Connection (Upload) for Source Data (TransXChange XML)
In a second step we will upload our TransXChange XML source data
|
1 2 3 4 5 |
curl --location --request POST "https://api.sonra.io/data_sources/transxchange" \ --header "Authorization: Bearer <access_token>" \ --form "source_type=uploaded_file" \ --form "file=@<file_path>" \ --form "data_type=xml" |
Example of output
|
1 2 3 4 5 6 7 8 |
{ "name" : "transxchange", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-1291d2e6-a29e-4f3d-bcf4-96911d1c4124", "size" : 295402, "create_date" : "2019-10-29T12:19:08.529+0000" } |
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.
|
1 2 3 4 |
curl --location --request POST "https://api.sonra.io/schema_sources/transxchange" \ --header "Authorization: Bearer <access_token>" \ --form "source_type=uploaded_file" \ --form "file=@<file_path>" |
Example of output
|
1 2 3 4 5 6 7 |
{ "name" : "transxchange", "source_type" : "uploaded_file", "path" : "file-2658313f-84e4-4a78-89fa-92aed83a0b39.zip", "size" : 457953, "create_date" : "2019-10-29T12:20:24.069+0000" } |
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.
|
1 2 3 4 5 6 7 |
curl --location --request POST "https://api.sonra.io/target_connections/transxchange" \ --header "Authorization: Bearer <access_token>" \ --form "target_type=mysql" \ --form "host=xxxxxxxxx" \ --form "username=user" \ --form "password=password" \ --form "database=database" |
Example of output
|
1 2 3 4 5 6 |
{ "name" : "transxchange", "target_type" : "mysql", "path" : "xxxxxxxxx", "create_date" : "2019-10-29T12:21:10.584+0000" } |
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.
|
1 2 3 4 5 |
curl --location --request POST "https://api.sonra.io/conversions/transxchange" \ --header "Authorization: Bearer <access_token>" \ --form "data_source=transxchange" \ --form "schema_source=transxchange" \ --form "target=transxchange" |
Example of output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
{ "name" : "transxchange", "schema_source_connection" : { "name" : "transxchange", "source_type" : "uploaded_file", "path" : "file-2658313f-84e4-4a78-89fa-92aed83a0b39.zip", "size" : 457953, "create_date" : "2019-10-29T12:20:24.069+0000" }, "data_source_connection" : { "name" : "transxchange", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-1291d2e6-a29e-4f3d-bcf4-96911d1c4124", "size" : 295402, "create_date" : "2019-10-29T12:19:08.529+0000" }, "target_connection" : { "name" : "transxchange", "target_type" : "mysql", "path" : "xxxxxxxxx", "create_date" : "2019-10-29T12:21:10.584+0000" }, "create_date" : "2019-10-29T12:22:03.666+0000", "status" : "I", "download_link" : null, "diagram_link" : null, "mapping_link" : null, "credit_usage" : null, "full_status" : "INITIALIZED" } |
Downloading ER Diagram
We can create and download an ER Diagram of the model that Flexter generated by making a GET call.
|
1 2 |
curl --location --request GET "https://api.sonra.io/conversions/transxchange" \ --header "Authorization: Bearer <access_token>" |
Example of output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
{ "name" : "transxchange", "schema_source_connection" : { "name" : "transxchange", "source_type" : "uploaded_file", "path" : "file-2658313f-84e4-4a78-89fa-92aed83a0b39.zip", "size" : 457953, "create_date" : "2019-10-29T12:20:24.069+0000" }, "data_source_connection" : { "name" : "transxchange", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-1291d2e6-a29e-4f3d-bcf4-96911d1c4124", "size" : 295402, "create_date" : "2019-10-29T12:19:08.529+0000" }, "target_connection" : { "name" : "transxchange", "target_type" : "mysql", "path" : "xxxxxxxxx", "create_date" : "2019-10-29T12:21:10.584+0000" }, "create_date" : "2019-10-29T12:22:03.666+0000", "status" : "C", "download_link" : null, "diagram_link" : "<ER Diagram Download Link>", "mapping_link" : "<Mapping Download Link>", "credit_usage" : 0, "full_status" : "COMPLETED" } |
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.
|
1 2 3 4 5 6 7 |
SELECT * FROM mariadb.Route RU INNER JOIN mariadb.RouteSectionRef RSF ON RU.PK_Route = RSF.FK_Route INNER JOIN mariadb.RouteSection RS ON RSF.RouteSectionRef = RS.id INNER JOIN mariadb.RouteLink RL ON RS.PK_RouteSection = RL.FK_RouteSection INNER JOIN mariadb.JourneyPatternTimingLink JPTL ON RL.id = JPTL.RouteLinkRef INNER JOIN mariadb.AnnotatedStopPointRef ASTR ON JPTL.From_StopPointRef = ASTR.StopPointRef |
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.
