Workflow Application: XMLTODATABASE usage

Development Workflow Applications
1

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.

See the XMLTODATABASE Workflow Application section in the WorkflowGen Administration Guide for complete instructions on how to use XMLTODATABASE and a list of possible execution errors.

XML transactions document

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.

Note: 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.

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. The following is an 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)

Notes

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

  • It is 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>
...

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 )