In this post we will guide you through the fast proces of how to convert HR Open Standard XML to SQL Server. For this process we will use Sonra’s data warehouse automation tool for XML, JSON, and industry data standards Flexter as a Service (FaaS). 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.

HR Open Standards Consortium

The HR Open Standards Consortium is the only independent, non-profit and volunteer-led organization that is dedicated to developing and promoting a standard suite of specifications that enables exchanges of human resource related data.
Standards are free and open to all HR professionals and organizations.
We will be using HR Open Standard 4.1.1 XML version. A version of the standard in JSON is also available. You can find both versions here.
The 4.1.1 XML version covers the following subject areas

  • Assessments XML schema
  • Benefits XML schema
  • Common XML schema
  • Compensation XML schema
  • Interviewing XML schema
  • Recruiting XML schema
  • Screening XML schema
  • Timecard XML schema
  • Wellness XML schema

We have downloaded one of the XML sample files.
Before we start with the conversion, we will separate our XML and XSD files into separate folders and then zip those folders.

Once finished we can start with the conversion of HR Open Standard XML to SQL Server.
[flexter_banner]

Processing 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.

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"
}

[flexter_button]

Step 2 – Define Source Connection (Upload) for Source Data (HR Open Standard XML)

In a second step we upload the HR Open Standard XML source data

curl --location --request POST "https://api.sonra.io/data_sources/hr-open" \
--header "Authorization: Bearer <access_token>" \
--form "source_type=uploaded_file" \
--form "[email protected]<file_path>" \
--form "data_type=xml"

Example of output

{
"name" : "hr-open",
"type" : "xml",
"source_type" : "uploaded_file",
"path" : "file-06e2f2b9-84cb-4332-ae60-e10f99bf8ffd",
"size" : 580746,
"create_date" : "2019-08-07T13:41:02.423+0000"
}

Step 3 – Optionally define Source Connection (Upload or S3) for Source Schema (XSD)

In next step we will define Source Schema by uploading XSD files.

curl --location --request POST "https://api.sonra.io/schema_sources/hr-open-xsd" \
--header "Authorization: Bearer <access_token>" \
--form "source_type=uploaded_file" \
--form "[email protected]<file_path>"

Example of output

{
"name" : "hr-open-xsd",
"source_type" : "uploaded_file",
"path" : "file-c6f72b27-ced4-42e1-a5e2-b5f2f4bb5f34.zip",
"size" : 991790,
"create_date" : "2019-08-20T11:46:30.498+0000"
}

[blogBannerFlexter]

Step 4 – Define Target Connection (SQL Server)

In step next step we will define Target Connection. In this example we convert our XML data to a relational format in SQL Server.
We give the Target Connection a name and supply various connection parameters to the SQL Server database.

curl --location --request POST "https://api.sonra.io/target_connections/hropen" \
--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

{
"name" : "hropen",
"target_type" : "sqlserver",
"path" : "xxxxxxxxxxxxxxxxxxxxx.eu-west-1.rds.amazonaws.com",
"create_date" : "2019-08-20T11:52:00.124+0000"
}

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

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

curl --location --request POST "https://api.sonra.io/conversions/hropen" \
--header "Authorization: Bearer <access_token>" \
--form "data_source=hr-open" \
--form "schema_source=hr-open-xsd" \
--form "target=hropen"

Example of output

{
"name" : "DataFlow-4055fabe-163d-4eb5-96ac-6e75ac5a6db6",
"schema_source_connection" : {
"name" : "hr-open-xsd",
"source_type" : "uploaded_file",
"path" : "file-c6f72b27-ced4-42e1-a5e2-b5f2f4bb5f34.zip",
"size" : 991790,
"create_date" : "2019-08-20T11:46:30.498+0000"
},
"data_source_connection" : {
"name" : "hr-open",
"type" : "xml",
"source_type" : "uploaded_file",
"path" : "file-2f44bd95-c8ef-4594-a1a9-3371a807fb91",
"size" : 580746,
"create_date" : "2019-08-20T11:45:11.048+0000"
},
"target_connection" : {
"name" : "hropen",
"target_type" : "sqlserver",
"path" : "xxxxxxxxxxxxxxxxxxxxx.eu-west-1.rds.amazonaws.com",
"create_date" : "2019-08-20T11:52:00.124+0000"
},
"create_date" : "2019-08-20T11:53:46.631+0000",
"status" : "I",
"download_link" : null,
"full_status" : "INITIALIZED"
}

Example of ER Diagram


All the blue dots are tables in the ER Diagram. There are also a lot of different data points.
ER Diagram can we downloaded here.
Now lets run an SQL Query

SELECT FILENAME
, wai.source_device -- A type to identify the device being used in a wellness program. The specific device (identifier or serial number).
, wai.source_vendor -- The vendor of the device.
, wai.periodType -- The period to which the measured activity was recorded.
, wai.categoryCode -- A wellness category being measured. e.g. walking, swimming, running, biking.
FROM Wellness w
JOIN Wellness_activities_item wai
ON wai.FK_Wellness = w.PK_Wellness

 

Conclusion

We have shows you how you how to convert HR Open Standard XML with Flexter in 5 easy steps. 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.
[faq_button]