Converting GDTF XML standard to MySQL

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 to process the GTDF XML standard.

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 Flexter

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to MySQL 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 (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 Flexter 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 Enterprise edition:
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.
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.