Convert FpML XML to Oracle Data Modeler with Enterprise Flexter
Today we will show you how easy it is to convert FpML XML files to relational tables in an Oracle database with the enterprise version of Flexter. We then reverse engineer the DDL into Oracle Data Modeler and visualise the target schema.
FpML XML Data
FpML ( Financial Products Markup Language) is an XML message standard for the OTC Derivates industry. The standard includes all categories of privately negotiated derivatives. We download the XML data from the FpML website.
Convert FpML XML to DDL Using Flexter
Flexter automatically converts complex XML to a database, text, or Hadoop (Impala, Hive, Parquet, ORC etc.). It supports any industry standard off the shelf.
- In a first step we analyse the XML files, generate statistics and a logical target schema.
|
1 |
$ xml2er samples/fpml/reporting/reporting-5-9_xml.zip |
- Next we generate the XML schema metadata based on the stats from the previous step
|
1 |
$ xsd2er -k1182 -g1 samples/fpml/reporting/reporting-5-9_xml.zip |
- And we get number of logical schemas
- In a next step we generate the schema in Oracle and process the data. We connect to Oracle via the JDBC driver. We enable the full SQL logs (-c switch). We use the parameter tee to save to log
|
1 |
$ xml2er -c -l573 samples/fpml/reporting/reporting-5-9_xml.zip -S o -o jdbc:oracle:thin:@//d1.sonra.io:1521/orcl -u fpml01 -p xmlFF |tee test.log |
- And finally we extract the DDL commands from the log. Saving it into file fpml_reporting-5-9_ddl.sql
|
1 |
$ grep -E '(CREATE|ALTER) TABLE|COMMENT ON COLUMN' test.log|sed -r 's/[0-9]+:\s*\{WARNING: Statement used to run SQL\}/;\n/g'|sed -r 's/batching\s+[0-9]+\s+statements:\s*;/\n\n/' > fpml_reporting-5-9_ddl.sql |
- Next we move to Oracle Data Modeler.
Importing FpML DDL to Oracle SQL Developer
- First we import DDL to Oracle SQL Developer
- Next we choose DDL file by clicking on the green + icon
- We select the file we want to import
- Pick Oracle Database 12c
- Next we save the Log
- Now we click on Merge
- And we are done. We get the entire XML file in Oracle SQL Developer
Conclusion
We have show you how easy it is to convert FpML XML with Flexter and import it to the Oracle SQL Developer. In a short amount of time you can achieve what would normally take you weeks or months.
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.
