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 database, Database 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:
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.
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 variables, normal XPath functions and even the built in Xpath-functions, when you setup the parameter. The use of a variable is covered here.
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:
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.
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 ‘/’)
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.
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.
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 |
---|---|
& | & |
< | < |
> | > |
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 "<" and use this expression instead:
SELECT * from employees where employeeNumber < 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 '<', so you need to write the select like below:
SELECT * from employees where employeeNumber <= 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 '>', so you need to write the select like below:
SELECT * from employees where employeeNumber >= 100
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: