Converting PRODML XML to a database (SQL Server)

September 27, 2019

For this blog post we have prepared a process of converting PRODML XML to SQL Server. We will be using Sonra’s data warehouse automation tool for XML, JSON, and industry data standards Flexter, which allows us to do this conversion pretty fast and in a few steps.

PRODML

PRODML is a combination of XML and Web Services based on the upstream oil and natural gas industry standards, from Energistics and the PRODML Special Interest Group (PRODML SIG).
It is a set of standards for optimizing the production of oil and gas wells, with focus on multiple data points (e.g. reservoir-wellbore, custody transfer point etc.).
PRODML enables industry to use instrumentations and software in all domains for oil and gas explorations and production, by achieving integration of technologies and making it possible for all those technologies to work together, which wasn’t the case often, because of incompatible data structures and design.
You can find PRODML example files here.

Processing masked XML with Flexter

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to SQL Server can be done 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 (PRODML XML)

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

Example of output

Step 4 – Define Target Connection (SQL Server)

Since we don’t have a Source Schema we skip the optional step of defining a Source Schema.
We define our Target connection. We give the Target Connection a name and supply various connection parameters to the SQL Server database.

Example of output

Step 5 – Convert XML data from Source Connection (Upload) to Target Connection (SQL Server)

In the last step we convert PRODML XML. Data will be written to SQL Server Target Connection.

Example of output

Example of ER Diagram


The ER Diagram can be download here.
Next we will run an SQL Query, which will give us the approval details of the recorded reports.

Conclusion

We have showed you how to convert PRODML XML data to SQL Server, with Flexter in an easy and fast way.
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.