Best Practices to Load XML Files into Database

Introduction

As promised in our last blog 5 Steps to Resolve Diacritical (á) Character Issue in Database Migration we are going to share another issue which we faced during the data integration process for one of our client. The issue which we are going to discuss here is a few best practices to load XML file into a database. We came across this issue while helping one of our large clients in gaming industry. The data was received as an XML file format from source end and needed to be loaded in database. Let’s see what we did to overcome this specific issue.

The process to Load XML Files into Database

We replaced the combination of existing process of using Linux + Oracle + ODI to transfer XML file into the DB, different ODI Programs to load different XML files into the DB with the following.

• A single DB Procedure not more than 10 lines to load the XML files into the Database.
• This DB procedure can be REUSED to load multiple XML files. The only change needed is the XML file name and the target table. XML definition is NOT NEEDED inside this DB procedure.
• This DB procedure also handles exceptions and hence can send out the correct error messages in case of errors in the XML file.
• This process replaces the combination of ODI /Linux programs to load the XML files hence giving a homogeneous solution to the issue.

Keep checking our blogs as we are going to come out with several other common issues in Data Migration and how we tackled the same.