XML.Import

Previous Next

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

Processes the row elements found in the XML document one-by-one in document order. The exact behaviour is "UPSERT" handling by default, but this 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 a filepath pointing to a file on the file system, in which case the optional FROM clause is typically omitted. If expression refers to a database column, the possible values for expression are:

column

OLD( column )

 

OLD() is not typical here. Other SQL functions than OLD() are NOT supported.

It is customary, but not mandatory, to list value parameter-list 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 all value-parameter pairs supported in the SELECT output lists passed to XML.Import. For each parameter, possible values are listed. 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 parameter and value are case-insensitive.        

value

parameter

Description

{ yes | no }

UseIOFormats

Value is a string and must be quoted, as in the example earlier in this help topic.

 

If you specify 'yes' for this parameter, the imported column values are checked against the IO format of the corresponding domain. Otherwise, they must use the format described in "XML Representation of Date Values".

{No
|ChangedColumns
|AllColumns
|NoCheckOnPk }

VerifyOriginalValues

Value is a string and must be quoted, as in the example earlier in this help topic.

 

For row elements with an Update or Delete instruction tag, this parameter specifies whether values in the XML document must be compared with values currently in the database before an attempt is made to execute the instruction.

See the "VerifyOriginalValues" section below for the meaning of each possible value.

{ yes | no }

XmlDeclaration

Value is a string and must be quoted, as in the example earlier in this help topic.

 

If you specify 'yes' for this parameter, the default Rules Engine encoding is returned as an XML processing instruction. Otherwise, no encoding processing instruction is added to the returned XML document.

VerifyOriginalValues

IMPORTANT: If the XML document being imported has a usoft-xml processing instruction specifying a verify-original-values attribute, then that attribute setting will silently override any setting specified for VerifyOriginalValues in the INVOKE XML.IMPORT statement.

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:

value

Description

No (the default)

If a record with the primary key value does not exist, an error is raised.

 

If a record with the primary key value does exist, old values in the XML (if any) are not checked against old values in the database.

 

NoCheckOnPk

If a record with the primary key value does not exist, the instruction is silently ignored.

 

If a record with the primary key value does exist, old values in the XML (if any) are not checked against old values in the database.

 

AllColumns

If a record with the primary key value does not exist, an error is raised.

 

If a record with the primary key value does exist, old values in the XML (if any) are checked against old values in the database. An error is raised if the values are not the same.

 

ChangedColumns

If a record with the primary key value does not exist, an error is raised.

 

If a record with the primary key value does exist, old values in the XML (if any) are checked against old values in the database but only for changed columns. An error is raised if the values are not the same.

 

A changed column is a column for which the instruction is to replace the old value by a different new value.

With an Update instruction tag, only columns mentioned in the <Update> child element are changed columns.

With a Delete instruction tag, all columns are changed columns.

Example

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. The format of this document is described here.

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.

Corrective single-record and multi-record constraints have executed.

Since only records inserted or updated directly by the import are included, any effects that corrective 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.

 

 

See also

How import XML is processed

Obsolete children on import

Generating key values for parent-child data on import

XML.Import: Known problems and workarounds