Enrich XML from database

Enrich XML from database

Enrich XML from database

It is possible to change the contents of an XML file and to add data from an external database with the advanced special workflow component: Enrich XML from database.

 

An alternative to this component is ng.databaseLookup, which can e.g. be used directly in the designer, in a transformation and in the workflow.

 

Related components: Enrich spooled file from databaseDatabase SQL select and Database SQL update.

 

A related workflow input is: From database with custom SQL, which starts a workflow with an SQL select.

 

In order to refer to a database a database driver must be setup.

 

The enrichment is defined in an xent file with the file extension .xent.

 

The parameters of the Enrich XML from database component are these:

 

NG2WorkflowEnrichXMLFromDatabase0001

 

After running the Enrich XML from database workflow component the XML file is actually not really changed, but the payload of the workflow is changed, so that it now contains the new, changed XML file and subsequent workflow elements can 'see' the added data.

 

 

Xent file

This is an xent file (with extension .xent), in which you can setup SQLs to add more data into the payload XML file and/or to copy/calculate nodes via XPath expressions.

The .xent file must be loaded in the transforms library (or a sub folder inside of this).

The Xent file format is described below in details.

 

Cache SQL results

This tells InterFormNG2 if the optional SQL results should be cached or not. If yes, then a bit more memory is used on the server to keep the SQL results for 30 seconds, which makes multiple (identical) SQL lookups perform much faster. If not cached, then any SQL lookup is run and not SQL results are reused.


Xent files

The xent file defines a lookup into a database of your choice via SQL. This can be called from the workflow via the Enrich XML from database workflow component.

 

A better alternative to the xent option is the built-in function, ng:databaseLookup, which is more flexible and can be used directly in the designer and in expressions in the workflow.

 

You can use variablesnormal XPath functions and even the built in Xpath-functions, when you setup the parameter. The use of a variable is covered here.

 

Prerequisite

A prerequisite for the Xent files to work is, that you have placed a fitting JDBC driver inside a specific directory:

 

On the Windows platform you should place the jdbc driver inside the subdirectories /jre/lib/ext of where InterFormNG2 is installed. That is default:

C:\Program Files\InterFormNG2\jre\lib\ext

 

On other platforms you should place the jdbc driver inside the directory of the JDK (Java Development Kit), that is used for InterFormNG2.

On the IBM i platform it can e.g. be: /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/lib/ext


This section contains these sub-sections:

 

1.Introduction to xent

2.How to refer to a database connection.

3.How to use workflow variables in xent.

4.How to trim a field for comparison

5.How to use e.g. less than or greater than for comparison in the SQL expression.

6.Option to insert extra nodes in the output with xent.

 

Introduction

Here is an example of how the contents can look for an Xent file:

<?xml version="1.0" encoding="UTF-8"?>

<enrich>

 <datasource 

         name="default" 

         type="jdbc"

         settingsName='MyIBMi'

 />

 <nodes path="/Root/Document">

<sql expression="select * from APFUDV/TESTFILE where NUMBER=?"

                 datasource="default">

                 <parameter>DocumentNo</parameter>

         </sql>

 </nodes>

</enrich>

 

The settingsName (MyIBMi in the example above) refers to a database connection. The database configuration is optional, but recommended as it can be used in order to be able to encrypt and secure the password used for the connection. It also makes it possible to consolidate all the database connections to other machines. The MyIBMi configuration used is shown here.

 

(The Xent file must be loaded into the transform library)

 

<?xml version="1.0" encoding="UTF-8"?>

<enrich>

 <datasource name="default" type="jdbc"

         driverClassName="com.ibm.as400.access.AS400JDBCDriver"

         url="jdbc:as400:192.168.250.214;naming=system;translate binary=true"

         user="myuser" password="mypassword" />

 

 <nodes path="/Root/Document">

<sql expression="select * from APFUDV/TESTFILE where NUMBER=?"

                 datasource="default">

                 <parameter>DocumentNo</parameter>

         </sql>

         <sql expression="select * from extraDet where source_number=?" datasource="default">

                 <parameter>concat(Source_No,'@{helloworld}')</parameter>

         </sql>

         <xpath>

<field expression="/Root/CompanyInfo/Company_Name">The_company</field>

                 <field expression="Paragraphs/Header/Line">Text</field>

         </xpath>

 </nodes>

</enrich>

 

(The 4 lines in the <xpath> section can be excluded (in italic). They are only needed, if you also want to change the layout of the XML file)

 

The Xent file above can be reduced, if you run InterFormNG2 directly on the AS400 / iSeries /IBMi, then we can change the contents above into this:

 

<?xml version="1.0" encoding="UTF-8"?>

<enrich>

 <datasource name="default" type="jdbc"

         driverClassName="com.ibm.as400.access.AS400JDBCDriver"

         url="jdbc:as400://localhost;naming=system;translate binary=true"

                                                  />

 

 <nodes path="/Root/Document">

                 <sql expression="select * from APFUDV/TESTFILE where NUMBER=?"

                 datasource="default">

                 <parameter>DocumentNo</parameter>

         </sql>

         <sql expression="select * from extraDet where source_number=?" datasource="default">

                 <parameter>concat(Source_No,'@{helloworld}')</parameter>

         </sql>

 </nodes>

</enrich>

 

(In the example above we refer to the local machine and do not specify the user/password, which means, that the SQL is done with the authority of the user profile, IFORMNG2, under which the InterFormNG2 service is running.)

 

The data source part defined the driver to use, the server as well as the user ID and password for validation.

 

The nodes part contains this:

1.The path defined is used for specifying the path for any nodes, that you are selecting in the input XML file to be used as keys for the lookup in the database. It also specifies the path to use when the new data is inserted. If the path selects multiple nodes, then the SQL expression and the xpath node will executed/inserted for each node.

2.An SQL expression to select data from the database. Here you can use a question mark (?) to select a node from the input XML file. The question marks are replaced by the parameters following the SQL expression in the same sequence, so in the example above the SQL expression will be:

 

expression="select * from APFUDV/TESTFILE where NUMBER=<DocumentNo>"

 

In the example above this Table (APFUDV/TESTFILE) can e.g.  used:

 

Number

Text1

Text2

Text3

1001

Flower Power

Sunny side of the street

1001 Luftballons

1003

Woody woodpecker

If you go down in the woods

today

1004

Dead herring

I smell something

fishy

 

You can have multiple parameter nodes - one for each field. Each parameter is retrieved from the XML file. You can state the path for this in two manners:

 

A relative path (relative to the path stated earlier). If the path is relative, then the value of the parameter should not begin with a ‘/’. This parameter is found in the path stated earlier: <parameter>DocumentNo</parameter>.

You can also refer to a subnode relative to this path like this:

<parameter>SubNode/DocumentNo</parameter>.

 

An absolute path. You just specify the complete path to the node e.g. like so:

<parameter>/Root/DocumentNo</parameter>

(Must start with ‘/’)

 

Use of Variables in Xent

You can use workflow variables in your Xent files for the parameters of an SQL expression. You should assign the value before the Xent file is executed e.g. with the workflow component, Set one Workflow variable. As you can see in the example earlier in this section you refer to a variable in the format: @{<variable>}. This expression will be replaced by the value of this workflow variable.

 

IBM i platform: Use a library list to select the IBM i files in the SQL expression

It is possible to dynamically select the files on the IBM i platform via a library list. You just need to exclude the library in the reference to the file. The setup of the library list is covered in the section, How to select files in SQL via a library list on IBM i on the database connection.

 

A few tips of the SQL expression:

The parameter, ? should not be included in '' - even if it is an alphanumeric value.

 

If you want to trim trailing/leading blanks from the parameter found in the input XML file you can consider this:

select * from APFUDV/TESTFILE where NUMBER=trim(concat('',?))

This suggestion may not work on all databases, so you will need to test it.

 

The parameter is a reference to a node in the input xml file - you cannot use xpath functions for this.

 

You cannot directly use these characters in the SQL expression: &,<,> as they are not allowed in an XML file, which the xent file really is.

So if you want to insert either of these characters in the SQL expression, then you need to refer to the table below for the substitutions:

 

Character to insert

Substitution string

&

&amp;

<

&lt;

>

&gt;

 

You e.g. need to use this information if you want to select records with a condition, that tests if a node is larger or less than a value.

 

If you e.g. want to use this SQL expression:

SELECT * from employees where employeeNumber < 100

 

Then you need to replace "<" with "&lt;" and use this expression instead:

SELECT * from employees where employeeNumber &lt; 100

 

The replacements for less than as well as greater are also used if you want to use the comparisons: less than or greater than.

 

If you want to test if a value is less or equal to 100, then you want this expression:

SELECT * from employees where employeeNumber <= 100

But again you need to replace '<' with '&lt;', so you need to write the select like below:

SELECT * from employees where employeeNumber &lt;= 100

 

If you want to test if a value is greater or equal to 100, then you want this expression:

SELECT * from employees where employeeNumber >= 100

But again you need to replace '>' with '&gt;', so you need to write the select like below:

SELECT * from employees where employeeNumber &gt;= 100

 

Additional xpath nodes

Optional xpath_data nodes can be included.

This can be used for copying nodes into new node(s) or even doing advanced xpath calculations and put the result back into the XML file. In the example above a node called ‘The_company’ is added with the value of the node, /Root/CompanyInfo/Company_Name from the xml file. A ‘Text’ node per each found Line node is added in the output XML file. For each xpath node added an occurence attribute is added - numbered from 1 and upwards.

 

With the settings above you will get an XML file with the contents like below:

 

InterFormNG_Xent_002


    • Related Articles

    • Enrich spooled file from database

      Enrich spooled file from database It is possible to change the contents of a spooled file (as known on the IBM i platform) and to add data from an external database with the advanced special workflow component: Enrich spooled file from database. You ...
    • Database SQL update

      The advanced, utilities workflow component, Database SQL update can be used in order to update or insert data in a table in a database. Enabling custom SQL Custom SQL needs to be enabled in the global System settings. It is divided into two sections: ...
    • Database SQL select

      The advanced, utilities workflow component, Database SQL select can be used in order to extract information from a table in a database. This component will replace the original contents of the payload with the retrieved data, so you can consider to ...
    • From database

      A workflow can be initiated by a job record in a database table. Only relational databases are supported. Please notice, that this input only supports the very specific table layout as described in the section, Database configuration, where in input ...
    • From database with custom SQL

      InterFormNG2 can with the input type: From database with custom SQL monitor a table and process all records in this table, that fits a specific selection. You can consider to combine this with the workflow component, Database SQL update in order to ...