Suppress Identical Values before a Repeated Element

Values of some columns may repeat in database records. This may be useful in the output to XML:

Sort records by identical values.
Suppress identical values before an element is repeated.

The XML file will have a compact form and be more transparent when transformed to a readable format.

Suppressing identical values does not affect the ability to import the data back to the database (when using an analytic DAD). The suppressed values will appear in each record after import.

Sorting Records for Output to XML

Exported records must first be sorted according to the column values that should be suppressed.

Use the ORDER BY clause in the query to sort records in a synthetic DAD.

Describe element sorting that are bound to individual tables in an analytic DAD. Fill the Order by field in the dialog and type the column name (or list of columns separated by commas). If a column name contains a comma, it must be enclosed in grave accents characters.

Value Suppression Examples

Example 1

The Customers table with content:

ID_CUST	FIRST_NAME	SURNAME	REGISTER_DATE
0	John	Smith	1997-02-25
1	Peter	White	1997-02-25
2	Charles	Black	2002-12-03
3	Peter	White	2003-07-05

We want to export customers sorted by registration date to XML. We design the following synthetic DAD (stored under the name Structuring 1):

i XML TOP: SELECT * FROM Customers ORDER BY register_date
E <list>
  E <customers> TABLE - base cursor -
    E <registered>
      Tx register_date
    E </registered>
    E <person> Multi_occurrence
      E <name>
        Tx first_name
      E </name>
      E <surname>
        Tx surname
      E </surname>
    E </person>
  E </customers>
E </list>

Sorting records by the register_date column is guaranteed by the ORDER BY register_date clause in the query stored in the DAD. Since the Repeatable element is checked (Multi_occurrence) for the person element, the XML output will work as follows:

If the value of the suppressed column register_date does not change, the person element will repeat.
If the value of the suppressed column register_date changes, the upper element that also has the Repeatable element checked or the upper element (customers) will be repeated.

Customers that registered on the same date are grouped as follows:

<?xml version="1.0" encoding="UTF-8"?>
<list>
 <customer>
  <registered>1997-02-25</registered>
  <person>
   <name>John</name>
   <surname>Smith</surname>
  </person>
  <person>
   <name>Peter</name>
   <surname>White</surname>
  </person>
 </customer>
 <customer>
  <registered>2002-12-03</registered>
  <person>
   <name>Charles</name>
   <surname>Black</surname>
  </person>
 </customer>
 <customer>
  <registered>2003-07-5</registered>
  <person>
   <name>Peter</name>
   <surname>White</surname>
  </person>
 </customer>
</list>

Example 2

Now we will export data from the same table to show repeated registrations from the same person. Data will be grouped according to the name and surname. We will design an analytic DAD suitable for data input and output (stored under the name Structuring 2).

The DAD is as follows:

i XML TOP
E <list>
  E <customer> TABLE Customers ORDER BY surname,first_name
    E <person>
      E <name>
        Tx Customers.first_name
      E </name>
      E <surname>
        Tx Customers.surname
      E </surname>
    E </person>
    E <registered> Multi_occurence
      Tx Customers.register_date
    E </registered>
  E </customers>
E </list>

Record sorting is guaranteed by specifying the Order by field in the customer element description (the Customers table is bound to that element). As the registered element has the Repeatable element checked, the XML output will work as follows:

If the value of suppressed columns first_name and surname does not change, the registered element will be repeated.
If the value of suppressed columns first_name and surname changes, the customer element will be repeated.

Customer information is sorted according to the name and surname:

<?xml version="1.0" encoding="UTF-8"?>
<list>
 <customer>
  <person>
   <name>Charles</name>
   <surname>Black</surname>
  </person>
  <registered>2002-12-03</registered>
 </customer>
 <customer>
  <person>
   <name>John</name>
   <surname>Smith</surname>
  </person>
  <registered>1997-02-25</registered>
 </customer>
 <customer>
  <person>
   <name>Peter</name>
   <surname>White</surname>
  </person>
  <registered>1997-02-25</registered>
  <registered>2003-07-5</registered>
 </customer>
</list>

Example 3

The same case as example 2. We will assume that there may be different people in the database with an identical name and surname. Repeated registrations will be detected by the repeated ID_CUST value.

This can be achieved by adding the ID_CUST item to the DAD, as an attribute or text of the ID element:

i XML TOP
E <list>
  E <customer> TABLE Customers ORDER BY surname,first_name
    E <person>
      E <name>
        Tx Customers.first_name
      E </name>
      E <surname>
        Tx Customers.surname
      E </surname>
      E <ID>
        Tx Customers.id_cust
      E </ID>
    E </person>
    E <registered> Multi_occurence
      Tx Customers.register_date
    E </registered>
  E </customers>
E </list>

Now let us assume that we do not want the ID_CUST entry to be in the XML file. We only want it to affect the XML file segmentation. Allow or deny the join of registrations with the same name and surname (a possible reason for this may be the fact that we want to allow data import using this DAD, the imported ID_CUST values will break index uniqueness).

In such case we must check the Repeat on change of checkbox and enter the table and ID_CUST column in the dialog of the element that should be repeated when then ID_CUST value changes. The following DAD will be created (stored under the name Structuring 3):

i XML TOP 
E <list>
  E <customer> TABLE Customers ORDER BY surname,first_name REPEAT ON CHANGE IN Customers.id_cust
    E <person>
      E <name>
        Tx Customers.first_name
      E </name>
      E <surname>
        Tx Customers.surname
      E </surname>
    E </person>
    E <registered> Multi_occurence
      Tx Customers.register_date
    E </registered>
  E </customers>
E </list>

Using the Repeat on change of option we may control how the element is repeated.

Value Suppression Principle

Some elements may repeat when exporting individual records from the database to XML. These elements have a new table bound to them (relation 1:N) and have the Repeatable element checked.

A search is performed on an element that is repeated. The search is performed by one of the following methods:

All values sent to XML in the previous record outside of this element are identical.
Some of the values sent to XML in the previous record inside this element are different.

If multiple elements fulfill these rules, the element on the lowest level is repeated. The XML file will be small, but there will be a large amount of repeated data.

Example 3 showed when it is needed to repeat another element. The Repeat on change of entry is used in this case to describe the repeating element.

Value suppression will not repeat the element on the highest level element on a lower level that contains the changed values, instead, it will repeat inside one occurrence of a higher level element that contains the fixed, suppressed value.

Encoding XML Data

XML Support

Data Transfer from Relational Tables