The xent file defines the lookup into the database of your choice. Here is an example of how the contents can look: (Setup for the demo Intro_demo.xml file).

 

<?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>

         <xpath>

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

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

         </xpath>

 </nodes>

</enrich>

 

The datasource 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:

 

 

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 ‘/’)

 

 

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 reference to a node in the input xml file - you cannot use xpath functions for this.

 

If you want to run xpath functions on the data found in the input data before running an SQL you can e.g. use two rules in the workflow like this:

 

 

The first rule, xpath is only run, if no new variables have been inserted (you need to be cautious as the wrong setup can create a loop and cause the same rule to be called endlessly), and this should have no action. The output folder should be set to a directory, that is monitored by InterFormNG.

 

The second rule, sql is only run, if an additional node has been added via the rule, xpath (but could also be conditioned by a specific input-folder, and this should normally include an action to generate the final output.

 

The first xent file (Demo_sql4_1.xent) looks like this:

 

<?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.206;naming=system;translate binary=true"

user="kse2" password="mypassword" />

<nodes path="/Root/Document">

<xpath>

<field expression="normalize-space(DocumentNo)">Trimmed</field>

</xpath>

</nodes>

</enrich>

 

The second xent file (Demo_sql4_2.xent) looks like this:

 

<?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.206;naming=system;translate binary=true"

user="kse2" password="mypassword" />

<nodes path="/Root/Document">

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

datasource="default">

<parameter>xpath_data/Trimmed</parameter>

</sql>

</nodes>

</enrich>

 

 

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.