Converting PRODML XML to a database (SQL Server)

Uli Bethke Flexter, SQL, XML

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 as a Service (FaaS), which allows us to do this conversion pretty fast and in a few steps.

FaaS follows a pay as you go model. Flexter is also available as a free edition and as an enterprise edition. The enterprise edition can be installed in the cloud or on-premise.

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 FaaS API

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to SQL Server can be done in a few simple steps. For more details please refer to the FaaS API documentation.

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

Convert XML/JSON automatically to a Database, Text, or Hadoop

No manual coding
Cut cost by up to 80%

Find out more

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. If you are interested in Flexter you can try out the free version online.

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.

 

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.