Converting HR Open Standard XML to SQL Server
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. The enterprise edition can be installed in the cloud or on-premise.
Table of Contents
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 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 (HR Open Standard XML)
In a second step we upload the HR Open Standard XML source data
|
1 2 3 4 5 |
curl --location --request POST "https://api.sonra.io/data_sources/hr-open" \ --header "Authorization: Bearer <access_token>" \ --form "source_type=uploaded_file" \ --form "file=@<file_path>" \ --form "data_type=xml" |
Example of output
|
1 2 3 4 5 6 7 8 |
{ "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.
|
1 2 3 4 |
curl --location --request POST "https://api.sonra.io/schema_sources/hr-open-xsd" \ --header "Authorization: Bearer <access_token>" \ --form "source_type=uploaded_file" \ --form "file=@<file_path>" |
Example of output
|
1 2 3 4 5 6 7 |
{ "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" } |
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.
|
1 2 3 4 5 6 7 |
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
|
1 2 3 4 5 6 |
{ "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.
|
1 2 3 4 5 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
{ "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
|
1 2 3 4 5 6 7 8 |
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.
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.
