Eight things you need to know about ISO 20022 XML Messages
In this post we have summarised eight important facts about ISO 20022 XML messages. At the end of the post we show you how can automate the conversion of large volumes of ISO 20022 XML documents to a relational database.
What is ISO 20022?
The ISO 20022 industry data standard is used in Financial Services. Its purpose is to exchange messages and data between financial institutions. The standard defines the meaning of common concepts and processes in finance. This is also called the semantics of the standard. It offers a common language simplifying the communication between financial actors. The business processes are represented in UML diagrams. You can download a detailed description of the various business processes from the full ISO 20022 catalogue page. Simply download the Message Definition Report (Msg Def Report) and extract the files.
ISO 20022 Message Definition Report
The Message Definition Report contains three files. In the Word document, you will find a detailed description of the business process including swim lane diagrams. The Excel file contains UML diagrams for the business process. The PDF describes the schema and syntax of the XML document. It explains the various XML elements and describes the various abbreviations.
ISO 20022 and XML
ISO20022 also standardises on the structure of how data is exchanged. This is also called the syntax of the standard. There is a logical representation of the message and a physical encoding of the message. ISO 20022 uses XML standards to encode the data. Below is a sample XML message
Don’t worry if you can’t make sense of the abbreviations and the naming conventions. We will cover the details in a moment.
ISO 20022 Overlapping and Competing Standards
There are a number of standards that compete or overlap with ISO 20022. Just like there are many human languages there are a variety of financial standards. You might have heard of FIX, FpML, XBRL, ISO 15022 etc. ISO 20022 is the most comprehensive industry data standard in finance. It covers all business processes of the financial industry. The vast majority of these data standards are based on XML.
ISO 20022 Business Areas
The ISO 20022 standard covers 20 business areas with more than 400 messages. Each area is identified by a four letter area code, e.g. payment initiation (PAIN), security settlement (SESE), account management (ACMT) etc. ISO 20022 messages are available for the complete payment chain. A big driver for adoption of the Payments is the Single European Payment Area (SEPA), which replaces payment processes in individual countries.
ISO 20022 Adoption
The ISO 20022 adoption report lists the various financial institutions that have adopted the ISO 20022 standard. We did some analysis on the data and as per May 2017 we have the following adoption stats.
Top 10 business areas adopted
Top 10 countries adopting ISO 20022
Top 10 Message Types
ISO 20022 Message Identifier
The ISO 20022 message identifiers correspond to the XML Schemas (XSDs).
Each message identifier is split into four parts.
1. Business Area
2. Message identifier. The message identifier is the code allocated to a specific message in a business area. For example, the account management area has several messages: 001 refers to an account opening instruction message, while 011 refers to an account request rejection message. You can find all the different ISO 20022 messages directly on the full catalogue.
3. Variant. A variant is a simplified version of a message. Certain financial institutions might decide that they only need a subset of the elements/attributes in a message. They can then go ahead and create what is called a variant of the global message definition. A particular submission process needs to be followed before a variant can be created. When the global message changes, e.g. an element is dropped then the variant needs to be adopted as well.
4. Version: Different versions cater for changes or modifications to the message, e.g. an element is dropped, added, or its name is changed.
Message Reports
From the full ISO 20022 catalogue page, you can download the message report. It is split into three parts. Part 1 describes the business process in detail (including swim lane diagrams). It also contains various sample XML messages and resolves the short codes in the XML element names into a meaningful description. For each sample XML message a detailed scenario is provided. Part 2 describes the XML schema and structure. Part 3 contains a UML diagram of the business area.
Processing ISO 20022 XML to a relational database (Oracle) for data analysis
Frequently, we need to query the data inside the ISO 20022 XML messages. This is easier said than done. XML is a good format to exchange messages but it is terrible for running queries against the data. If we want to create reports against ISO 20022 data we first need to convert it to a database or a big data store such as Spark or Hadoop.
We have created a solution that fully automates this painful conversion process. It is called Flexter and comes in as an enterprise edition for on-premise or cloud installations. Flexter enterprise is used by large enterprises such as Volkswagen or Lufthansa.
For the purpose of illustrating the conversion process of ISO 20022 XML messages we will use Flexter. Converting XML/JSON to Oracle 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
As a sample ISO 20022 message we picked an ATMDeviceReportV03 dcoument (caam.001.001.03). Let’s go through the steps to convert this message.
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 (ISO 20022)
In a second step we upload the ISO 20022 XML source data
|
1 2 3 4 5 |
curl --location --request POST "https://api.sonra.io/data_sources/iso20022" --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" : "iso20022", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-dc37e1dd-9327-4b80-b05f-ec79d519ee56", "size" : 42283, "create_date" : "2019-08-28T15:59:05.876+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.
|
1 2 3 4 5 6 7 |
curl --location --request POST "https://api.sonra.io/target_connections/iso-20022-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
|
1 2 3 4 5 6 |
{ "name" : "iso-20022-oracle", "target_type" : "oracle", "path" : "xxxxxxxxxxxxxxxxx.eu-west-1.rds.amazonaws.com", "create_date" : "2019-08-28T16:00:21.259+0000" } |
Step 5 – Convert XML data from Source Connection (Upload) to Target Connection (Oracle)
In last step we convert ISO 20022. Data will be written to Oracle Target Connection.
|
1 2 3 4 |
curl --location --request POST "https://api.sonra.io/conversions/iso20022" --header "Authorization: Bearer <access_token>" --form "data_source=iso20022" --form "target=iso-20022-oracle" |
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 |
{ "name" : "DataFlow-7c9c0561-b468-4344-a68e-cbb03ff770a7", "schema_source_connection" : null, "data_source_connection" : { "name" : "iso20022", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-dc37e1dd-9327-4b80-b05f-ec79d519ee56", "size" : 42283, "create_date" : "2019-08-28T15:59:05.876+0000" }, "target_connection" : { "name" : "iso-20022-oracle", "target_type" : "oracle", "path" : "xxxxxxxxxxxxxxxxx.eu-west-1.rds.amazonaws.com", "create_date" : "2019-08-28T16:00:21.259+0000" }, "create_date" : "2019-08-28T16:01:55.097+0000", "status" : "I", "download_link" : null, "full_status" : "INITIALIZED" } |
Let’s have a look at the output that Flexter generated. First an ER diagram
Example of ER Diagram
You can see ER Diagram in details by downloading the file here.
Next we will run an SQL Query to analyse the data.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT d.ATMDvcRpt_Hdr_MsgFctn_Fctn , d.ATMDvcRpt_Hdr_PrtcolVrsn , d.ATMDvcRpt_Hdr_CreDtTm , stadr.* , irdn2.* FROM Document d JOIN SctyTrlr_AuthntcdData_Rcpt stadr ON stadr.FK_Document = d.PK_Document JOIN Issr_RltvDstngshdNm2 irdn2 ON irdn2.FK_Rcpt = stadr.PK_SctyTrlr_AuthntcdData_Rcp |
Flexter summary
It just took a couple of minutes what normally would take a couple of days.
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 with any questions.
Which data formats apart from XML also give you the heebie jeebies and need to be liberated? Please leave a comment below or reach out to us.
