Relational Linking of Data from the XML File

Data stored in different tables are often linked by key values in a relational database. If we are transferring data from one database to another by means of an XML file, we will assume that the relation will be preserved or a new relation will be created on the target database.

The key values that link individual records can be:

either they are contained in the XML file,
or they can be created during import by means of tools that are described bellow.

If the XML file import is utilized for data acquisition from multiple sources into the target database, it is usually better not to include the linking key values to the XML file. The linking key value must be unique within the parent table and, when merging data from various resources, duplicity can occur. That is why it is better to generate them during the import.

The DAD examples listed bellow can be utilized both for data export and import as well. Prior to export, it is necessary to populate tables using the Data for export procedure; prior to import, it is recommended to clear data from tables using the Clear before import procedure.

Creation of Relational Link with Key Value Stored in the XML File

If two tables are linked in the DAD design using condition T1.C1=T2.C2, this will be used during import. If XML contains the C1 value and it does not contain the C2 value, then the C1 value will be written also into the C2 column within the corresponding record in T2. On the other hand, if XML contains the C2 value and it does not contain the C1 value, then the C2 value will be written also into the C1 column within the corresponding record in T1.

If T1 is a parent table, and if multiple records from the child table T2 are linked to it, and the C1 value is not contained in the XML file, then T1 record will use the C2 value from the first element occurrence describing the record in T2.

The XML DAD example named Join key 1 demonstrates transfer of value from the parent table to the child table. DAD Join key 2 demonstrates transfer of value from the child table to the parent one.

Keys Consisting of Multiple Columns

The rules described above are fully valid also for linking keys consisting of multiple columns. Transfer of the key value from the parent table to the child table or vice versa is realized using the same rules for each column that is used separately within the key.

Creation of a Relational Link with a New Key Value

Generation of unique keys is a property that is not described in the older SQL language standards and, despite that it exists in each database, it is processed on each occasion differently. That is why when importing data to a database linked by means of ODBC, it is necessary to make use of properties of particular database system. 3 ways to generate linking keys will be described bellow. In the DAD design, the columns containing linking keys are listed neither as a text in elements, nor as attribute values. They are listed in element description where a new table is relationally linked.

Default 602SQL Way

This way makes use of the 602SQL capability to generate a key when inserting a record into a table and, thereafter, to read value of this key. It cannot be used for ODBC data sources.

You will proceed like follows: Specify in the design of database tables a default value to the column that is a key, so that unique values are generated. E.g., make use of the sequence object or of the default UNIQUE value.

During import, a new key is generated when inserting a record into parent table. Value of this key is copied into the relevant column in all corresponding records that are inserted into the child table.

Examples XML DAD named Join key 2 demonstrate creation of a new linking value during the import.

Evaluation of Expression of the Generating Key prior to Record Insertion

This way is intended for databases where it is possible to define a generator of unique values that is not linked to a table. Prior to inserting a record into a table, generator is invoked and the obtained value is appended to the values from the XML file during record insertion. Then it is also used in the child records as a link function to the parent record.

In the element describing dialog where the new table is linked, press the Generating the key value button. Then select the second generation version in the Generating keys dialog, enter the table column to store the key value and the generating expression.

In the databases like Oracle or 602SQL, the new value from the generator is referenced by sequence_name.nextval. This value can be obtained using the output parameter or using the column value from the query. In the first case, use the generating expression, e.g.:

SET ?=sequence_name.nextval

SELECT sequence_name.nextval INTO ?

according to syntax admitted by the data source; in the second case, e.g. (for Oracle):

SELECT sequence_name.nextval FROM dual

Obtaining the Key Value that Came to Being during Record Insertion

This way is intended for databases where it is possible to define generator of unique values that is linked to table column, and where it is possible to query the last generated value. When inserting a record into the table, the server automatically invokes the generator and the obtained value will appear in that record together with values from the XML file. Then the XML file importing module evaluates the specified expression and the key value is loaded, so that it could be used also in the child records as a referencing function to the parent record.

In the element describing dialog where the new table is linked, press the Generating the key value button. Then select the third generation version in the Generating keys dialog, enter the table column to store the key value and the expression determining the value.

In databases like Oracle or 602SQL, the last value from the generator is referenced by sequence_name.currval, in other databases you can use words like IDENTITY, LAST_INSERT_ID etc. To obtain this value, use some of the syntaxes mentioned above, e.g. (for MySQL - the linked table contains a column with key having a label AUTO_INCREMENT):

SELECT LAST_INSERT_ID()

Data Transfer from Relational Tables