Uli Bethke
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.
|
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 (PRODML XML)
In a second step we will upload our PRODML XML source data
|
1 2 3 4 5 |
curl --location --request POST "https://api.sonra.io/data_sources/prodml" \ --header "Authorization: Bearer <access_token>" \ --form "source_type=uploaded_file" \ --form "data_type=xml" |
Example of output
|
1 2 3 4 5 6 7 8 |
{ "name" : "prodml", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-06e2f2b9-84cb-4332-ae60-e10f99bf8ffd", "size" : 37500, "create_date" : "2019-09-25T13:41:02.423+0000" } |
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.
|
1 2 3 4 5 6 7 |
curl --location --request POST "https://api.sonra.io/target_connections/prodml" \ --header "Authorization: Bearer <access_token>" \ --form "target_type=sqlserver" \ --form "host=<example.com>" \ --form "username=<sql_user>" \ --form "password=<sql_password>" \ --form "database=<master>" |
Example of output
|
1 2 3 4 5 6 |
{ "name" : "prodml", "target_type" : "sqlserver", "path" : "xxxxxxxxxxxxxxxxxxxxxxxxxx1.rds.amazonaws.com", "create_date" : "2019-09-25T16:50:30.143+0000" } |
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.
|
1 2 3 4 |
curl --location --request POST "https://api.sonra.io/conversions/prodml" \ --header "Authorization: Bearer <access_token>" \ --form "data_source=prodml" \ --form "target=prodml" |
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 |
{ "name" : "DataFlow-1011e136-3168-4879-93a6-1754658d6de6", "schema_source_connection" : null, "data_source_connection" : { "name" : "prodml", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-fa622ed6-6c93-4bd7-9901-b5d122187da3", "size" : 5215515, "create_date" : "2019-09-25T13:47:50.480+0000" }, "target_connection" : { "name" : "prodml", "target_type" : "prodml", "path" : "xxxxxxxxxxxxxxxxxxxxxxxxxx1.rds.amazonaws.com", "create_date" : "2019-09-25T13:34:10.769+0000" }, "create_date" : "2019-09-25T13:48:04.705+0000", "status" : "I", "download_link" : null, "full_status" : "INITIALIZED" } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT r.kind ,r.Operator_Name as OperatorFullName ,r.Approver_PersonName_First as ApproverFirstName ,r.Approver_PersonName_Last as ApproverLastName ,r.ApprovalDate ,rar.Role as ApproverRole ,rir.Role as IssuedRole FROM test.dbo.Report as r ,test.dbo.Report_Approver_Role as rar ,test.dbo.Report_IssuedBy_Role as rir WHERE r.PK_Report = rar.FK_Report AND r.PK_Report= rir.FK_Report |
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.
