Converting HR Open Standard XML to SQL Server

Uli Bethke Flexter, XML, XSD

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.

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.

Example of output

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

Example of output

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.

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

Example of output

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.

Example of output

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

 

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.

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.