Converting GDTF XML standard to MySQL

Uli Bethke Flexter

In this post we will guide you through the easiest way of processing your XML data to a relational format on MySQL . We will be using Flexter as a Service (FaaS) to process the GTDF XML standard. FaaS follows a monthly subscription model.

Flexter is available in three editions. An enterprise edition, which can be installed on-premise. A managed SaaS edition Flexter as a Service, which you can access by API. You can also try Flexter for free.

GDTF standard

The GDTF Groups have jointly developed the General Device Type Format (GDTF) standard, which creates a unified definition for the exchange of device data between consoles, CAD and previsualization software, for the operation of intelligent luminaires.

The standard provides a consistent way to adapt new fixtures and devices in the lighting industry.

Processing XML with FaaS API

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to MySQL can be performed in a few simple steps. For more details please refer to the FaaS API documentation. We have recently also added a Command Line Interface, which you can use with Python.

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 (GDTF XML)

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

Example of output

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

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

Example of output

Step 4 - Define Target Connection (MySQL)

In the next step we will define the Target Connection. In this example we convert our XML data to a relational format in MySQL.

We give the Target Connection a name and supply various connection parameters to the MySQL.

Example of output

Step 5 - Convert XML data from Source Connection (Upload) to Target Connection (MySQL)

In the last step we convert GDTF XML. Data will be written to the MySQL Target Connection.

Example of output

Downloading ER Diagram

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

Example of output

 

You can download the ER Diagram of our GDTF standard here.

Querying GDTF in MySQL

In this step we will run an sql query which will show us what options geometries have for a specific mode of illumination.

Conclusion

In this post you saw how easy it is to process and convert your GDTF data to MySQL database and without a need for any manual coding. Other databases can be used to achieve the same process and with the same ease.

Why did we create Flexter?

Flexter was born from our own frustration of converting XML and JSON documents into a data warehouse. We saw many projects fail or run over budget. Why waste weeks of converting XML/JSON and risking failure instead of focusing on delivering real value to your business users? We think that the time spent writing manual code to convert the data to It just takes too much time of converting the data

Our customers are delighted with Flexter. Hear what they say

“This will save us weeks”

“You did in one day what we could not achieve in 3 years”

And this is what Ralph Kimball, one of the fathers of data warehousing has to say about XML conversion:

“The construction of an XML parser is a project in itself - not to be attempted by the data warehouse team”. Ralph Kimball, ETL Toolkit

We provide Flexter in three editions:

You can try out the free version of Flexter for JSON or XML.

Our enterprise edition can be installed on a single node or for very large volumes of XML on a cluster of servers.

Our SaaS edition, Flexter as a Service (FaaS), is managed by Sonra. You can use the Flexter API to convert your data.

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.

Last but not least we have open sourced Paranoid, our solution to obfuscate data in XML and JSON documents

Who uses Flexter?

Companies in travel, finance, healthcare, automotive, finance, and insurance industry use Flexter.

 

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.