XML.Import

Imports data presented in the USoft XML single-table format or the USoft XML multi-table format.

Imports base64-encoded binary data (of the BLOB data type).

IgnoreGUK parameter has been added.

Processes the row elements found in the XML document one-by-one in document order. The default behaviour is "UPSERT" handling:

• A record is INSERTed if no record with the same primary key is found in the target table.
• No action is taken if a record with the same primary key, and no different values in other columns, is found in the target table.
• A record is UPDATEd if a record with the same primary key, but different values in other columns, is found in the target table.
• No record is DELETEd.

This “UPSERT” handling may be modified by the usoft-xml processing instruction supplied at the top of the import document, by DML instruction tags supplied inside the row elements, and by the VerifyOriginalValues and UseIOFormats parameters of XML.Import itself.

If a Document Type Definition (DTD) is specified in the XML document, the document contents must be in accordance with this DTD.

Syntax

INVOKE XML.Import WITH
SELECT      value    parameter
,           value    parameter ...
,           expression XMLDocument
FROM        table

The required SELECT output-list is made up of 0 or more value parameter pairs and 1 required (SQL) expression, which may be named by the optional XMLDocument alias. Possible values for the value parameter pairs are listed in the table below.

Expression is usually either a filepath pointing to a file on the file system, in which case the optional FROM clause is typically omitted (Example 1), or an inline XML document (Example 3 further down in this article). If expression refers to a database column (Example 2), the possible values for expression are:

column
OLD( column )

Other SQL functions than OLD() are not supported. OLD() is not typical in this context.

It is customary, but not mandatory, to mention value parameter pairs before expression.

Example 1

INVOKE  XML.Import WITH
SELECT  'c:\temp\tours.xml'

Example 2

INVOKE    XML.Import WITH
SELECT    'yes'          UseIOFormats
,         description    XMLDocument
FROM      tour

Parameters

This table lists value parameter pairs supported in the SELECT output lists passed to XML.Import. Possible values are listed by parameter; the default value (if any) is underlined. The use of each parameter is optional. If you do not specify a given parameter and it has a default value associated with it, that is the value applied.

Both value and parameter are case-insensitive.

VerifyOriginalValues

Setting the VerifyOriginalValues parameter allows optimistic locking mechanisms and prevents lost updates if different client applications change the same information concurrently.

The VerifyOriginalValues parameter has an effect only on records in the XML document that contain an Update or Delete instruction tag. It determines whether or not the Rules Engine will perform 2 types of check before attempting the import instructions:

• A check whether a record with the primary key value mentioned in the XML actually exists in the database.
• A check that old values mentioned in the XML are the same as the old values in the database record.

If any of these checks is performed and fails, a blocking error is raised, causing the import operation to be unsuccessful.

Old values are non-primary-key values mentioned in the XML or found in the database before the Update or Delete. In the USoft XML format, old values are optional. By default, in an Update instruction, the old values are mentioned for each of the columns that are to be updated.

The values of the VerifyOriginalValues parameter have the following meaning:

Example 3

In this example, a blocking error is raised if one or more of the following apply:

• There is no PERSON database record with ID = 112.
• There is no PERSON database record with ID = 203.
• There is a PERSON database record with ID = 112, but either its FAMILY_NAME value is other than "Smith" or its FIRST_NAME value is other than "John" (or both).
• There is a PERSON database record with ID = 203, but either its FAMILY_NAME value is other than "Sutcliff" or its FIRST_NAME value is other than "Bill" (or both).

INVOKE    XML.Import WITH
SELECT    '<Persons>
             <PERSON ID="112" FAMILY_NAME="Smith" FIRST_NAME="John">
               <Update FIRST_NAME="Johnny"/>
             </PERSON>
             <PERSON ID="203" FAMILY_NAME="Sutcliff" FIRST_NAME="Bill"/>
               <Delete/>
             </PERSON>
           </Persons>' XmlDocument
,         'AllColumns' VerifyOriginalValues

The returning XML document

The XML.Import method returns a result XML document.

This document contains an element for each record that has been inserted or updated directly by the import. Column values shown in the result document are those that apply after the record has been processed:

• Sequence numbers have been calculated.
• Productive single-record and multi-record constraints have executed.

Since only records inserted or updated directly by the import are included, any effects that productive multi-record constraints may have had on other records are not visible in the result document.

If the import has not caused any record to be inserted or updated, the result document contains only a root element.