Reading XML files from FTP server and inserting them into Oracle database in IBM API Connect
Here in this blog, we are going to learn how to read XML files from the FTP server and insert them into Oracle database in IBM API Connect.
Requirement:
Read the file from the remote FTP server and insert the data in the file into a Database.
Purpose:
The purpose of this task is to read the file from a remote FTP server and insert the Data present in the file into a Database.
Pre-Requisites:
- An FTP Server with Read and Write Permission
- IBM API Connect Management.
- An Oracle Database.
Procedure:
- Create a directory in FTP Server and Place the file attached below.
<?xml version="1.0" encoding="UTF-8"?> <bulk_payments> <customer> <name>Rakesh Sir</name> <account_number>1234567890</account_number> <transaction> <id>1</id> <beneficiary_name>Jane Smith</beneficiary_name> <beneficiary_account>0987654321</beneficiary_account> <amount>1000.00</amount> <currency>USD</currency> <description>Payment for services rendered</description> </transaction> <transaction> <id>2</id> <beneficiary_name>Bob Johnson</beneficiary_name> <beneficiary_account>9876543210</beneficiary_account> <amount>500.00</amount> <currency>USD</currency> <description>Payment for goods delivered</description> </transaction> <transaction> <id>3</id> <beneficiary_name>Alice Brown</beneficiary_name> <beneficiary_account>0123456789</beneficiary_account> <amount>250.00</amount> <currency>USD</currency> <description>Payment for consulting services</description> </transaction> </customer></bulk_payments>
- Create a Table in the Database using the below DDL:
CREATE TABLE "SYSTEM"."BULK_PAYMENTS_TRANSACTIONS" ( "ID" VARCHAR2(2000 BYTE), "NAME" VARCHAR2(2000 BYTE), "ACCOUNT_NUMBER" VARCHAR2(2000 BYTE), "BENEFICIARY_NAME" VARCHAR2(2000 BYTE), "BENEFICIARY_ACCOUNT" VARCHAR2(2000 BYTE), "AMOUNT" VARCHAR2(2000 BYTE), "CURRENCY" VARCHAR2(2000 BYTE), "DISCRIPTION" VARCHAR2(2000 BYTE) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" ;
Login to Datapower.
- Go to the APIConnect Domain.
- In the Search bar type ‘SQL DataSource’
- Add a Name to it and configure the necessary Details like IP, PORT, SID, username, Password and etc of the Oracle Database.
- Make sure your DataSource is in ‘UP’ mode.
Login to API Manager.
- Click on create new API.
- Select “Import from Existing Yaml File”
- Upload the YAML Attached Below.
Testing:
Since it is a GET Method, just try to invoke the API Using Post Man or API Manager TEST or EXPLORER window. You will get the Success response if the API is invoked properly and Database is in the ‘UP’ Condition.
Now try to go to the Database and execute the below query to get the data of the TABLE.
“select * from bulk_payments_transactions”
Response from Database when Query executed is in the form of a pdf file attached below check it out:
Conclusion:
We have successfully read the XML file data and inserted it into the database without any issues.
Look after this PPT in SlideShow mode to know more about what exactly happening internally.
Click on this slide and keep on pressing enter button.
NOTE:
- IPs may be changed at the time when you are invoking please check and reconfirm before testing.
- The file name is hardcoded in the XSLT there in API Assembly please check and compare whether the naming conventions are matched or Not with the filename in the FTP Location and in the Code at this point ‘ftp://user:user@XXX.XX.XX.XXX:21/test/Bulk_Payment_Customer_Transaction_Details.xml’.
Future Explorations:
We need to implement the same for JSON, SOAP, and also CSV files also.