Adding XML data to a populated temp-table

If you need to merge data in an ABL temp-table with XML data, you can control the way in which the AVM handles duplicate unique key conflicts with the read-mode parameter. In this example, a new temp-table is set up with definition from the Customer table and two new records. This set-up code is stored in an include file. Note that the first record uses a customer number that conflicts with one in the XML document. The second record has no conflict with the XML document, as shown:

/* pi-tfx-ttSetup-5.i */
/* Creates a new static temp-table and creates two new records for it. */
DEFINE TEMP-TABLE ttCustomer NO-UNDO
   /* Field and index definitions. */

CREATE ttCustomer.
ASSIGN
  ttCustomer.Name     = "Extreme XML Sports"
  ttCustomer.CustNum = 1
  ttCustomer.Balance = 111.11
  ttCustomer.Country = "USA"
  ttCustomer.Comments = "Enthusiastic new customer!".

CREATE ttCustomer.
ASSIGN
  ttCustomer.Name     = "Low-Impact XML Sports"
  ttCustomer.CustNum = 4
  ttCustomer.Balance = 444.44
  ttCustomer.Country = "USA"
  ttCustomer.Comments = "Mellow new customer.".

<?xml version="1.0"?>
<ttCustomer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ttCustRow>
<CustNum>1</CustNum>
<Country>USA</Country>
<Name>Lift Tours</Name>
    ...
   </ttCustRow>
<ttCustRow>
<CustNum>2</CustNum>
<Country>Finland</Country>
<Name>Urpon Frisbee</Name>
    ,,,
</ttCustRow>
<ttCustRow>
<CustNum>3</CustNum>
<Country>USA</Country>
<Name>Hoops </Name>
    ...
</ttCustRow>
</ttCustomer>

To illustrate the different results obtained with each read-mode option, there is a unique index defined for the CustNum field. There is a record in both the include file and the XML file that uses the same CustNum value (1). This is known as a duplicate unique key conflict. The different read modes respond to such a conflict in different ways.

* pi-tfx-read-5.p */
/* Merges records in a temp-table with records from an XML file.*/
{pi-tfx-parameterVarDefs.i}
{pi-tfx-ttSetup-5.i}

DEFINE VARIABLE lReturn AS LOGICAL NO-UNDO.

ASSIGN
  cSourceType             = "FILE"
  cFile                   = "ttCustomer.xml"
  cReadMode               = "EMPTY"
  cSchemaLocation         = ?
  lOverrideDefaultMapping = ?
  cFieldTypeMapping       = ?
  cVerifySchemaMode       = ?.

lReturn = TEMP-TABLE ttCustomer:READ-XML(cSourceType, cFile, cReadMode,
  cSchemaLocation, lOverrideDefaultMapping, cFieldTypeMapping,
  cVerifySchemaMode).
IF lReturn THEN
FOR EACH ttCustomer:
  DISPLAY ttCustomer.CustNum ttCustomer.Name FORMAT "X(30)".
END.

Table 24. Read mode examples
Read mode	List result	Explanation
EMPTY		The method empties the existing data from the temp-table. It then reads the XML document and adds records to the temp-table. Since the temp-table is empty, there is no duplicate key conflict. The result is a temp-table with records exclusively from the XML document.
APPEND		The method reads the XML document and adds records to the temp-table. Due to the duplicate key conflict between the record in the temp-table and the record in the XML with a CustNum of 1, the method stops loading records and stops. Records added before a conflict is detected are retained. The AVM displays the error message shown.
MERGE		The method reads the XML document and adds records to the temp-table. Due to the duplicate key conflict between the record in the temp-table and the record in the XML with a CustNum of 1, the record from the XML is ignored. The result is all the existing records from the temp-table, plus all of the records from the XML except for the record with a CustNum of 1.
REPLACE		The method reads the XML document and adds records to the temp-table. Due to the duplicate key conflict between the record in the temp-table and the record in the XML with a CustNum of 1, the record from the XML replaces the record in the temp-table. The result is all the existing records from the temp-table except for the record with a CustNum of 1, plus all of the records from the XML.