How to persist XML data to IRIS Database using IRIS Interoperability

The InterSystems IRIS has a series of facilitators to capture, persist, interoperate, and generate analytical information from data in XML format. This article will demonstrate how to do the following:

1. Capture XML (via a file in our example);
2. Process the data captured in interoperability;
3. Persist XML in persistent entities/tables;
4. Create analytical views for the captured XML data.

Capture XML data

The InterSystems IRIS has many built-in adapters to capture data, including the next ones:

1. File Adapter: used to get files from network folders.
2. FTP Adapter: employed to obtain files from FTP/SFTP servers. 
3. SOAP/Web Services Adapter: utilized to receive XML data from Web services operations.
4. HTTP adapters: operated to acquire XML data and files from HTTP Endpoints.
5. Other uncommon adapters to attain XML, e.g., mail adapters. You can find them at https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls....

To capture data using interoperability productions, you need to configure a production and use a Business Service associated with a proper adapter. The most common Business Services are listed below:

1. EnsLib.XML.Object.Service.FileService. It is my preferred option since it utilizes a File Adapter to obtain an XML file from the network and load it on XML schema-mapped objects. It is the approach we will employ in this article.
2. EnsLib.XML.Object.Service.FTPService. It operates an FTP Adapter to acquire an XML file from an FTP server and load it on XML schema-mapped objects.
3. EnsLib.XML.FileService. It uses a File Adapter to get an XML file from the network and load it on a File Stream.
4. EnsLib.XML.FTPService. It employs an FTP Adapter to obtain an XML file from the FTP server and load it on a File Stream.
5. EnsLib.HTTP.GenericService and EnsLib.HTTP.Service. It operates an HTTP adapter to consume an HTTP endpoint and acquire XML content.
6. EnsLib.SOAP.GenericService and EnsLib.SOAP.Service. It uses a SOAP adapter to consume a SOAP endpoint and receive XML content.

 

Persist and/or Interoperate captured XML

To persist or send (inter-operate) the XML data, you can use all the adapters mentioned above, but you must also include the SQL Adapter. It is utilized to persist data into RDBMS databases, e.g., the IRIS database. Below you can check out the list of common adapters employed to interoperate:

1. SQL Adapter: to interoperate data to the target databases.
2. Kafka Adapter: to interoperate data asynchronously into Kafka topics.
3. REST Adapter: to interoperate with REST APIs.
4. PEX Adapters: to interoperate with native Java, DotNet, or Python components.  

To interoperate or persist data utilizing interoperability productions, you must configure a production and employ a Business Operation associated with a proper adapter. The most commonly used Business Operations are the following:

1. EnsLib.SQL.Operation.GenericOperation: used to persist captured data into SQL and IRIS databases.
2. EnsLib.Kafka.Operation: utilized to publish the captured/processed data into Kafka topics. 
3. EnsLib.REST.GenericOperation: employed to send captured/processed data to REST APIs methods, allowing integrations via REST APIs.
4. EnsLib.SOAP.GenericOperation: exploited to send captured/processed data to SOAP Web Service methods, permitting integrations through SOAP Web services.
5. EnsLib.PEX.BusinessOperation: operated to send captured/processed data to native Java, DotNet, and Python components.
6. EnsLib.XML.Object.Operation.FTPOperation and EnsLib.XML.Object.Operation.FileOperation: used to save XML data as a file from object data into FTP servers and Filesystem locations.

Between the data captured using Business Services and the data sent through Business Operations, it is possible to operate a Business Process to implement business rules/logic or to map and translate data format and structure from the Business Service to the Business Operation. The most popular Business Processes types are the next ones:

1. Ens.BusinessProcessBPL. It implements business logic with the help of the BPL visual language.
2. EnsLib.MsgRouter.RoutingEngine. It is employed to implement map and translation logic to convert captured data to the protocol/structure used by Business Operations.
3. EnsLib.PEX.BusinessProcess. It is utilized to implement business logic via Java, DotNet, or Python.

Resuming the InterSystems IRIS interoperation system, we have the following:

Now, to illustrate and materialize XML data interoperation, we will engage a sample from an Open Exchange application.

XML Interoperation: a sample

Install the sample

1. Clone/git pull the repo into any local directory

$ git clone https://github.com/yurimarx/iris-xml-sample.git

2. Open the terminal in this directory and run:

$ docker-compose build

3. Run the IRIS container with your project:

$ docker-compose up -d

 

Start the sample production

1. Open the link http://localhost:52796/csp/user/EnsPortal.ProductionConfig.zen?$NAMESPACE=USER&$NAMESPACE=USER& 
2. Click the Start button and see all topics in green:

Configure the VSCode for this project

1. Open the source code in VSCode:

2. On the footer, look for the ObjectScript button (only if not connected yet):

3. Click it and select Toggle Connection on top:

4. Now you have the VSCode and IRIS server connected on the USER namespace:

Process an XML sample

1. Go to the Explorer tab:

2. Select the books.xml and copy it inside the xml_input folder:

3. After a second or two the books.xml file will be processed and removed from the folder (VSCode shows the file as a stripped one and removes it).
4. Now you can see the results in the Production editor: http://localhost:52796/csp/user/EnsPortal.ProductionConfig.zen?$NAMESPACE=USER&$NAMESPACE=USER&
5. Go to the Messages tab and click the first session in the list:

6. Check out the Content tab:

7. Now, go to the SQL editor: http://localhost:52796/csp/sys/exp/%25CSP.UI.Portal.SQL.Home.zen?$NAMESPACE=USER&$NAMESPACE=USER 

8. Execute the following SQL and look at the results:

SELECT
ID, author, description, genre, price, publish, title, totalDays
FROM dc_XmlDemo.Catalog

At this point, we will understand the source code for this sample.

Behind the Scenes: the source code

Creating a class mapping for the XML Schema

1. Open the books.xml file and copy its content:

2. We need to create an XML Schema for this XML file in case it does not exist yet. I utilized this site: https://www.liquid-technologies.com/online-xml-to-xsd-converter. However, there are many other available options online. So, paste the XML content and click Generate Schema.

3. Copy the generated XML Schema and create the books.xsd file with its content:

4. Go to the IRIS Terminal. To do that, click the docker:iris:52796[USER] button on the footer and select the option Open Terminal in Docker:

5. Write the commands as indicated below:

USER>set reader = ##class(%XML.Utils.SchemaReader).%New()              
USER>do reader.Process("/home/irisowner/dev/xml_input/books.xsd","dc.XmlDemo.Schemas")

6. This IRIS class method creates for you classes mapping for the XML structure, allowing you to access the XML via classes, properties, and methods:

Creating the persistent class to store books ingested from XML data

1. Create the Catalog.cls in the src/dc/XmlDemo folder with the next content:

Class dc.XmlDemo.Catalog Extends %Persistent
{

Property author As %String(MAXLEN = 200);
Property title As %String;
Property genre As %String;
Property price As %Double;
Property publish As %Date;
Property totalDays As %Integer;
Property description As %String(MAXLEN = "");
}

2. This sentence is not clear to me. Maybe the author wanted to say: "This class extends the %Persistent class, allowing us to store persistent data in the database."
3. The properties here reflect the XML data. However, one of them, totalDays, is not present in the XML data. It will be calculated with the number of days from publishing to the current day. You should save this class to create it on the IRIS Server.

Creating the credentials to access the IRIS database from the production

1. Go to Interoperability > Configure > Credentials and create the IrisCreds credential:

• ID: IrisCreds
• User Name: _SYSTEM
• Password: SYS

2. Click Save.

Creating the Interoperability Production

1. Proceed to Interoperability > List > Productions:

2. Create a new Production by clicking the New button.
3. Fill in the values according to the image below and click the OK button:

4. Click the + button near Services:

5. Set the options as shown below:

6. Fill the file paths to the XML with the following data:

• File Path: /home/irisowner/dev/xml_input/
• File Spec: *.xml
• Archive Path: /home/irisowner/dev/xml_output/

7. Fill in the Class Name and Element Name to get book elements data at runtime:

8. Click the Apply button.
9. At this point, we should use Java Gateway because we will do a JDBC connection with the IRIS server database to send SQL insert commands and save the data. This approach is valid for any database vendor, including Oracle, SQL Server, DB2, MySQL and PostgreSQL, when you need to persist XML data to an SQL table.
10. Click the + button near Services again. Fill in the fields as illustrated below and click the OK button:

11. Pick the JavaGateway and fill in the Class Path field with the following data:

• Class Path: /usr/irissys/dev/java/lib/1.8/*.jar

12. This path contains the IRIS Database JDBC driver.
13. Click the Apply button.
14. Click the + button near Operations.
15. Fill in the values and click the OK button:

16. Choose SqlOperation and fill in the DSN with the User (namespace where the book SQL table is available).
17. Select IrisCreds in the Credentials field.
18. Write the following SQL command in the Query field:

insert into dc_XmlDemo.Catalog(author, description, genre, price, publish, title,totalDays) values (?,?,?,?,DATE(?),?,DATEDIFF('d',?,CURRENT_DATE))

19. Fill RequestClass with the value dc.XmlDemo.Schemas.book:

20. Click + near Add and click the List button near the X button to choose the field for the first ‘?’ parameter:

21. Select *author and click the OK button:

22. Include more parameters to get the results displayed below:

23. It is critical to check if your values have * before the field.
24. Click the Apply button.
25. Now, pick XmlFileService again and set the field Target Config Names to SqlOperation. It will allow the service to receive the XML data and send the values to the SqlOperation saved in the database.

26. Stop the production created in the example before starting the one you developed:

27. Click Open and Stop it:

28. Select the production you need: 
29. Select your new production, open it, and start it.
30. On VSCode, copy the books.xml file in the xml_input folder to test your production.
31. See the results as indicated in the Process XML Sample section and Enjoy!