Converting Financial Information eXchange XML (FIXML) to MS SQL Server

Uli Bethke SQL, XML

FIX is a messaging standard used for trades in the financial services industry. The FIX Trading Community is the independent body behind the FIX messaging standard. FIX messages are encoded in XML. The FIXML schema files (XSD) together with the specification are available for download from the FIX website.

In this blog post we will use the Flexter XML converter to parse a FIXML message with an embedded FpML message to MS SQL Server.

Flexter, our ETL tool for XML, can generate a target schema from an XML file or a combination of XML and XML schema (XSD) files. In our example we process data based on the FIXML schema. Both the XML files and the XSD are available and we use the information from both files to generate the target schema in the SQL Server database.

 

Now that we have gathered statistics from our XML sample we can generate the logical target schema with the xsd2er command line tool using the -k switch (-k is shortcut for --use-stats), where we will use the origin id generated in the previous step.

Let’s go through the steps

Now we use the Logical Schema ID (Logical:45 and origin: 68) to convert the XML data.

When the command is ready, removing --skip or -s, allows us to process the data. We can directly send the parsed output to our SQL Server target database, which we will create in the below steps.

 

To install and configure SQL Server on Linux, we will proceed as below and run the following commands in a terminal to install the mssql-server package.

To create a database, you need to connect with a tool that can run Transact-SQL statements on the SQL Server. The following steps install the SQL Server command-line tools: sqlcmd and bcp.

 

Use the following steps to install the mssql-tools on Ubuntu.

The following steps use sqlcmd to locally connect to your new SQL Server instance. Run sqlcmd with parameters for your SQL Server name (-S), the user name (-U), and the password (-P). In this tutorial, you are connecting locally, so the server name is localhost. The user name is SA and the password is the one you provided for the SA account during setup.

Guide to install the SQL Server for other distributions is given here. Also, we can use SQl Server docker version as given here.

Next, we will use sqlcmd to create a new database where we will export the data extracted using Flexter.

 

From the sqlcmd command prompt, paste the following Transact-SQL command to create a test database:

Now as we can extract the data to the output SQL Server connection. We can use the following options to generate the output as desired

Initially we use the the primary key to partition the data we are extracting using the -I option, We can also parse and extract the entire XML content if we skip the -I option. Here we will use the logical schema id 45, created in the previous step.

As you can see Flexter is loading the FIXML output directly to the target database.

We can run basic queries to see if the data has been parsed correctly

We can run similar SQL Queries with the data to do basic data analysis with the data extracted from flexter. The text version of the tables are attached and available for download. Also, the ER Diagram for the tables is available for download.

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, holds an Oracle ACE award, 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.