Convert FpML XML to Oracle Data Modeler with Enterprise Flexter

Uli Bethke Flexter, XML

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.

  • Next we generate the XML schema metadata based on the stats from the previous step

  • 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

  • And finally we extract the DDL commands from the log. Saving it into file 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.

 

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.