XMLTODATABASE

Overview

The XMLTODATABASE workflow application lets you export data from any XML document to one or several databases. It is a synchronous system application that does not require the user to take action. The configuration of the SQL transaction is done via an XML document that enables SQL queries to be performed on ODBC, OLEDB, or other custom data sources.

How it works

The WorkflowGen engine calls the XMLTODATABASE application with the context and the parameters. XMLTODATABASE uses the context and the parameters to get the following:

• The XML data document to export

• The transactions document, which contains:

  • Information on how to connect to the database

  • Information on where the data should be taken in the XML data document (using XPaths)

After XMLTODATABASE has gathered all of the above information, it is ready to perform the export. Once the export is complete, it returns the context to WorkflowGen so that the workflow may continue.

Description of the XML transactions document

Overview

The XML transactions document specifies the SQL commands that will be executed on the databases. It is used for two main functions: connecting to the database, and mapping the fields of the query to the fields of the XML data document. Remember that the XML data document (generally named FORM_DATA) can be constructed in many ways. For this reason, XPaths are used to map the database fields to the XML fields.

Structure

Any one XMLTODATABASE activity can have an unlimited number of databases and an unlimited number of commands per database. This means that the export can be done to several databases, and each database can have more than one command.

📌 Example of an XML transactions document

<transactions>
    <transaction name="">
        <databases>
            <database name="" connectionstringname="" connectionstring="" provider="" transaction="">
                <command type="" loop="" xpath="">
                [QUERY HERE]
                 </command>
            </database>
        </databases>
    </transaction>
</transactions>

Attributes

transaction node

Attribute	Description
name	Used to map the transaction to the XMLTODATABASE activity/action In WorkflowGen, a TRANSACTION parameter (TEXT type data) must be defined for every XMLTODATABASE activity/action. The text entered in the parameter must match the transaction name attribute in order to use the right transaction for the right activity.

database node

Attribute	Description
name	Name of the database used for the transaction
connectionstringname	Contains the name of a connection string that is centrally managed in the WorkflowGen web.config file (see the example below)
connectionstring	Contains the ConnectionString to connect to the database
provider	Used to inform XMLTODATABASE which namespace should be used to create the access to the database (System.Data.OleDb or System.Data.Odbc) ✏️ Note: This attribute can only be used with the connectionstring attribute.
transaction	Used to inform XMLTODATABASE whether to use a DB transaction for the export (values: yes or no)

• You can use either the connectionstringname or connectionstring attributes, but not both.

• It's strongly recommended to use a connection name rather than a connection string to simplify multi-environment management.

command node

Attribute	Description
type	Used to inform XMLTODATABASE what kind of command is being performed Possible values can be any valid SQL command, except if it is calling a stored procedure. In this case, the type must be PROCEDURE.
loop	Used to perform a batch of commands using all of the values returned by the XPath in the xpath attribute of the command node (possible values: yes or no) For example, if the loop attribute is set to yes, and the XPath returns 10 results, the XPath contained in the command query will be executed 10 times (once for each result). If the loop attribute is set to no, the command will be executed only once with the first node returned by the XPath contained in the query.
xpath	Used to specify a part of the XPaths used in the query

Connection string name example

XMLTODATABASE transaction file

...
<database name="MYDB" connectionstringname="MYDBSOURCE">
..

WorkflowGen web.config file

..
<connectionStrings>
    <add name="MYDBSOURCE" connectionString="Data Source=MYSQLSERVER;Initial Catalog=MYDB;User ID=user;password=pwd;" providerName="System.Data.SqlClient"/>
<connectionString>
...

Location of the XML transactions document

There are four ways to define an XML transaction for an XMLTODATABASE action. In all of the following cases, the transaction document is defined as follows:

<transactions>
    <transaction name="MY_TRANSACTION">
        ...
    </transaction>
</transactions>

1. XMLTODATABASE first looks for a TRANSACTIONS_TEXT parameter.

2. If this parameter is not found, it then looks for a TRANSACTIONS_FILE parameter.

3. If this parameter is not found either, it then looks for a file with your transaction’s name.

4. If this file is not not found either, it then parses the Transactions.xml file to find a node with your transaction’s name.

The priority order, then, is as follows:

1. TRANSACTIONS_TEXT parameter.

2. TRANSACTIONS_FILE parameter.

3. MyTransaction.xml file.

4. Transactions.xml file.

In the common file

In this case, the transaction file is located in the \wfgen\App_Data\Files\XmlToDatabase folder, and named Transactions.xml. The XMLTODATABASE application parses this file to find your transaction.

The XMLTODATABASE action contains the following parameters:

• TRANSACTION: IN direction text type data

• XML: IN direction file type data

In a specific file

If your transaction is not found inside the Transactions.xml file, the XMLTODATABASE application looks for an .xml file with the same name as your transaction. The transaction document should be created in the MY_TRANSACTION.xml file.

The XMLTODATABASE action contains the TRANSACTION and XML parameters.

You can change from the common transaction file to a specific transaction file without updating your process definition by removing the transaction from the Transactions.xml file and adding it in a file named with your transaction’s name.

In a process file data type

If you can’t access the web server's \App_Data folder, or if you want to include your transaction in your process definition (to be exported and shared by an XPDL file), you can upload your transaction file in a process file data type. To do this:

1. Create a process file data type and upload your MY_TRANSACTION.xml file.

2. Edit your XMLTODATABASE action, add a new parameter named TRANSACTIONS_FILE, and send the value of your process data.

In a process text data type

If you can’t access the \App_Data folder of the web server, or if you want to include your transaction in your process definition (to be exported and shared by an XPDL file), you can write your transaction within a process text data type.

As of WorkflowGen version 7.15.0, the XML transaction contained in a TEXT process data no longer has a 4000-character limit for MS SQL Server database.

To do this:

1. Create a process text data type and write your transaction as a default value.

2. Edit your XMLTODATABASE action, add a new parameter named TRANSACTIONS_TEXT, and send the value of your process data:

It is not necessary to create process data. You can send the value of your XML transaction within the TRANSACTION_TEXT parameter.

Date and numeric field formatting

You can specify which fields must be formatted as date or numeric values using the following parameters:

Parameter	Description
XML_FIELDS_DATE	List of date type fields; must contain a list of XPath expressions separated by , (comma) characters
XML_FIELDS_NUMERIC	List of numeric type fields; must contain a list of XPath expressions separated by , (comma) characters
XML_LOCALE	Culture code to use to format the date and numeric values (e.g. en-GB or en-US)

Date format generated into the SQL queries

DATE type fields are formatted as follows: yyyy-mm-dd hh:MM:ss

Numerical format generated into the SQL queries

Numeric fields are formatted as follows: XXXX.XX

📌 Example

• Rule: All the nodes found in the XML document with the name REQUEST_DATE and the specific node situated at /MyData/MyExample/Date_Field will be formatted as date

  • Method: XML_FIELDS_DATE = //*/REQUEST_DATE, /MyData/MyExample/Date_Field

Details of the transaction execution

The SQL commands can be launched as transactions. If errors occur, a rollback is launched and the initial state of the database is restored. The value of the transaction attribute of the database node must be set to yes.

If the transaction handles many databases, the execution of the commands will be multi-transactional, meaning if an error occurs in a command in one of the databases, a rollback on all database transactions performed will be launched.

Test mode

The transactions can be tested before being executed on the database. The value of the constant XmlToDatabaseTestMode in the configuration file must be set to Y.

If this constant is set to Y and you have specified using transactions on the database nodes, the transactions will not be committed at the end of the execution.

Description of the log file

If the XmlToDatabaseEnableTrace parameter in the configuration file of the web service is set to Y, a log file will be created in the \wfgen\App_Data\LogFiles\XmlToDatabase directory.

The log entries are in the following format:

Date; [Database name;] Transaction name; SQL query; Execution result

Execution result values are:

• OK if the query was run successfully, or

• ERROR: Error code - Error description if an error occurred.

📌 Examples

02/12/2020 4:41:23 PM; ACCESS; TEST_TRANS; INSERT INTO DATA ...; OK

02/12/2020 4:41:24 PM; ACCESS; TEST_TRANS; DELETE FROM DATA2; ERROR: 1234-Table was not found

Possible execution errors

The following errors can occur during the execution of the transactions:

Error	Cause
The XML file parameter is missing	The required XML parameter has a null value or is not completed.
The XML context cannot be empty	The XMLTODATABASE application must receive a context from WorkflowGen; it cannot be used without being part of a WorkflowGen process.
XML parsing error (1)	The XML parameter is pointing to an invalid XML file.
XML parsing error (2)	The XML transaction file is an invalid XML file.
XML parsing error. The provider attribute is required	The provider attribute of the database node is not present in the transaction.
Unable to commit transactions	The commit operation has not succeeded.
Error while opening log file	The log file cannot be opened.
The TRANSACTION parameter is missing	The required TRANSACTION parameter has a null value or is not completed.
The definition of the transaction has not been found	The transactions.xml file has no transaction with the name attribute corresponding to the value of the TRANSACTION parameter.
SQL Instruction not valid: the parameter has not been found	The PARAM {parameter name} has not been found.
XML parsing error. The connectionstring attribute is required	The connectionstring attribute has not been specified in the database node.
The XML field was expected to be date, but its value is not valid	One of the XPaths in the XML_FIELDS_DATE that was passed refers to a field that is not a date field.
The XML field was expected to be numeric, but its value is not valid	One of the XPaths in the XML_FIELDS_NUMERIC that was passed refers to a field that is not a numeric field.
The XPath is not a valid XPath expression	One of the XPaths in your transaction file is not a valid XPath expression.
The XPath passed as a parameter is not a valid XPath expression	One of the XPaths in your XML_FIELDS_DATE or XML_FIELDS_NUMERIC parameters is not a valid XPath expression.
Error while loading the XML file: File was not found	The XML parameter is not pointing to an XML file.
Database connection error	The connection to the database cannot be established. Check the validity of the connection string contained in the database node's connectionstring attribute. ✏️ Note: It is strongly recommended to use a connection name rather than a connection string to simplify multi-environment management.
Error during the execution of the SQL command	The execution of the SQL command failed. Check the syntax of the related SQL command.
Error during the execution of the SQL loop command	The execution of the SQL command failed. Check the syntax of the related SQL command.
SQL instruction not valid: the XML field has not been found.	One of the XPath expressions you have used in your parameters of the command returned no fields. Check the syntax of your XPath expressions.
The culture code for the XML file in the param XML_LOCALE is not valid.	Check that the XML_LOCALE parameter is in the right format (e.g. en-US or fr-CA).

Export examples

📌 Example 1

XML file structure:

<data>
    <request_number>1</request_number>
    <request_first_name>John</request_first_name>
    <request_last_name>Smith</request_last_name>
</data>

The command node could look like this:

<command type="INSERT" loop="no" xpath="/data/">
    INSERT INTO EXAMPLE (request_number, request_first_name, request_last_name, request_date)
    VALUES (
        {XPATH:request_number},
        '{XPATH:request_first_name}',   
        '{XPATH:request_last_name}',
        '{PARAM:REQUEST_DATE}')
</command>

The XPATH: tags in the query will be replaced with /data/, which is the XPath attribute value.

The PARAM: tag is used to identify a WorkflowGen parameter instead of using an XML field.

• XPaths that return no nodes will use null values.

• It is not possible to use the qualified XPath by using XPATH:: instead of XPATH:.

📌 Example 2

The following is a more complex example using the loop property.

XML file structure:

<Library>
    <publishers>
        <publisher>Grasset</publisher>
    </publishers>
    <AUTHORS>
        <author id="100">
            <name>Stephen King</name>
            <description>Horror novel author</description>
            <birth_date>1947-09-21</birth_date>
        </author>
        <author id="200">
            <name>John Smith</name>
            <description>Description of the author</description>
            <birth_date>1972-06-06</birth_date>
        </author>
    </AUTHORS>
    <BOOKS>
        <book id="38">
            <title>Title of book #38</title>
            <description>Description of the book #38</description>
            <author id="100"/>
        </book>
        <book id="39">
            <title>Title of book #39</title>
            <description>Description of the book #39</description>
            <author id="200"/>
        </book>
        <book id="40">
            <title>Title of book #40</title>
            <description />    // Will insert a NULL value automatically
            <author id=""/>    // Will insert a NULL value automatically
        </book>
    </BOOKS>
</Library>

The command nodes could look like this:

<command loop="yes" type="INSERT" xpath="/Library/AUTHORS/">
    INSERT INTO AUTHORS
        ([AUTHOR_ID],[AUTHOR_NAME],[AUTHOR_DESC],[AUTHOR_PUBLISHER])
    VALUES ({XPATH:author/@id},
        '{XPATH:author/name}',
        '{XPATH:author/description}',
        '{XPATH::/Library/publishers/publisher}' )
</command>
<command loop="yes" type="INSERT" xpath="/Library/BOOKS/">
    INSERT INTO BOOKS 
        ([BOOK_ID],[BOOK_TITLE],[BOOK_DESCRIPTION],[BOOK_AUTHOR_ID])
    VALUES ({XPATH:book/@id},
        '{XPATH:book/title}',
        '{XPATH:book/description}',
        '{XPATH:book/author/@id}')
</command>

You must use two : (colon) characters when you don't want to use the qualified XPath in the command node, as shown in {XPATH::/Library/publishers/publisher} above.