# XMLTODATABASE Workflow Application ## 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 ```markup [QUERY HERE] ``` ### 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](#connection-string-name-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`) | {% hint style="info" %} * 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. {% endhint %} #### `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 ```markup ... .. ``` #### WorkflowGen `web.config` file ```markup .. ... ``` ## 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: ```markup ... ``` 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. {% hint style="info" %} 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. {% endhint %} ### 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. {% hint style="info" %} 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. {% endhint %} 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: {% hint style="info" %} It is not necessary to create process data. You can send the value of your XML transaction within the `TRANSACTION_TEXT` parameter. {% endhint %} ## 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`. {% hint style="info" %} 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. {% endhint %} ## 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 ``` ```markup 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: ```markup 1 John Smith ``` The `command` node could look like this: ```markup 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}') ``` 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. {% hint style="info" %} * XPaths that return no nodes will use `null` values.
* It is not possible to use the qualified XPath by using `XPATH::` instead of `XPATH:`. {% endhint %} #### đź“Ś Example 2 The following is a more complex example using the `loop` property. XML file structure: ```markup Grasset Stephen King Horror novel author 1947-09-21 John Smith Description of the author 1972-06-06 Title of book #38 Description of the book #38 Title of book #39 Description of the book #39 Title of book #40 // Will insert a NULL value automatically // Will insert a NULL value automatically ``` The `command` nodes could look like this: ```markup 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}' ) 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}') ``` {% hint style="info" %} 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. {% endhint %}