Masking and converting Guidewire XML to Oracle

In this post we will guide you through the process of masking Guidewire XML data and converting it to Oracle. This process normally take days or weeks. With our solution this will just take a few minutes.

We will be using Sonra’s masking tool Paranoid (open source) to obfuscate the data. We will use Flexter as a Service (FaaS) to convert the data to a relational format. FaaS follows a pay as you go model. Flexter is also available as a free edition and in an enterprise edition. The enterprise edition can be installed in the cloud or on-premise.

Guidewire

Guidewire is a software publisher, that offers an industry platform for property and casualty insurance carriers.

It has three products that helps Insurance companies to minimize their IT workflow.

• Claim Center - handles Claims life cycle
• Policy Center - helps with buying policies based on specific needs
• Billing Center - when combined with Claim and Policy centers, eases out Financial transactions

Oracle

Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation.

Masking Guidewire XML

Since nobody likes sharing their personal data, we will start by masking our Guidewire XML file first.

We have already shown how to install Paranoid in our Masking Sabre XML post.

To mask data we have to provide a path to our file and a path to an output location. Paranoid will create the folder automatically, no need to create it first.

paranoid -i <file path> -o <output file path>

As you can see in the image it only took a couple of seconds to mask all of the values in the XML file. Optionally Paranoid has the feature to mask individual elements inside an XML document and also multiple XML documents in one go.

Let’s have a look at our file after masking

After checking that everything is masked we can go and zip our masked file and continue with processing it with Flexter.

Processing masked XML with FaaS API

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to Oracle 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.

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

{

"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 (Guidewire XML)

In a second step we upload the Guidewire XML source data

curl --location --request POST "https://api.sonra.io/data_sources/guidewire" \

--header "Authorization: Bearer <access_token>" \

--form "source_type=uploaded_file" \

--form "file=@<file_path>" \

--form "data_type=xml"

Example of output

{

"name" : "guidewire",

"type" : "xml",

"source_type" : "uploaded_file",

"path" : "file-06e2f2b9-84cb-4332-ae60-e10f99bf8ffd",

"size" : 37500,

"create_date" : "2019-08-07T13:41:02.423+0000"

}

Step 4 - Define Target Connection (Oracle)

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 Oracle database.

curl --location --request POST "https://api.sonra.io/target_connections/oracle" \

--header "Authorization: Bearer <access_token>" \

--form "target_type=oracle" \

--form "host=xxxxxxxxxxxxxxxxx.eu-west-1.rds.amazonaws.com" \

--form "username=oracle_user" \

--form "password=oracle_password" \

--form "database=orcl"

Example of output

{

"name" : "oracle",

"target_type" : "oracle",

"path" : "xxxxxxxxxxxxxxxxx.eu-west-1.rds.amazonaws.com",

"create_date" : "2019-08-07T13:42:41.766+0000"

}

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

In a final step we convert Guidewire XML data. Data will be written to Oracle Target Connection.

curl --location --request POST "https://api.sonra.io/conversions/guidewire" \

--header "Authorization: Bearer <access_token>" \

--form "data_source=guidewire" \

--form "target=oracle"

Example of output

{

"name" : "DataFlow-44b04ef7-9ea5-4093-aaff-eb57b71b2e59",

"schema_source_connection" : null,

"data_source_connection" : {

"name" : "guidewire",

"type" : "xml",

"source_type" : "uploaded_file",

"path" : "file-06e2f2b9-84cb-4332-ae60-e10f99bf8ffd",

"size" : 37500,

"create_date" : "2019-08-07T13:41:02.423+0000"

},

"target_connection" : {

"name" : "oracle",

"target_type" : "oracle",

"path" : "xxxxxxxxxxxxxxxxx.eu-west-1.rds.amazonaws.com",

"create_date" : "2019-08-07T13:42:41.766+0000"

},

"create_date" : "2019-08-07T13:45:26.553+0000",

"status" : "I",

"download_link" : null,

"full_status" : "INITIALIZED"

}

Example of ER Diagram

You can download the ER Diagram for the converted Guidewire XML file here.

Next we will run SQL Query where we will select all Addresses for contact names.

SELECT CONTACTS_ENTRY_CONTACT_NAME, CITY, ADDRESSLINE1

FROM CLAIM c

JOIN CONTACT_ALLADDRESSES_ENTRY cae

ON cae.FK_CLAIM = c.PK_CLAIM

Conclusion

We have masked Guidewire XML and converted it to Oracle Database in few simple steps. This process is easily done with Paranoid and Flexter in couple of steps without investing a lot of time.

You can try out the free version of Flexter 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.