 |
|
|
 | Suppressing Identical Values before Repeating Elements |  | XML Support | Selecting Records for Output to XML Using Variables |  |
If multiple tables are involved in a data transfer, a synthetic DAD will be different from an analytic DAD.
A synthetic DAD allows you to export data to XML from the result of a query written in the SQL language. The query may combine data from multiple tables.
The query is written to the DAD global parameters. The query is first sent to the 602SQL Server, data from the result is read and then transformed to XML when exporting.
The client application may parameterize the DAD, choosing only data for export that fulfill certain conditions.
The client application may also open a cursor with the same column structure as the query written in the DAD, and pass it to the XML export function. In this case, only the cursor data is exported and the query in the DAD is ignored.
If the structure of the created XML file should correspond with the relational dependencies in the database (if record R is represented by element E, then the records with relational dependencies to R will be represented by elements nested in E), you can use the method of suppressing identical values before a repeated element.
You may bind tables via a relation in a synthetic DAD just like an analytic DAD (described below).
We have the following tables: Customers, Orders and OrderLines. The relation 1:N is binding the Customers and Orders tables (a customer may have multiple orders) and the Orders and OrderLines tables (an order may have multiple items).
Example table data:
| Customers | Orders | OrderLines | |||||||||||||||||||||||||||||||||||
|
|
|
A query that naturally joins the contents of these tables:
SELECT *
FROM Customers JOIN
Orders JOIN OrderLines
ON (Orders.id_ord=OrderLines.id_ordlin)
ON (Customers.id_cust=Orders.id_cust)
DAD for exporting (stored as Join synth 1):
i XML TOP: SELECT * FROM Customers JOIN ....
E <list>
E <customer> TABLE - base cursor -
E <surname>
Tx surname
E </surname>
E <order>
E <order_id>
Tx id_ord
E </order_id>
E <item>
E <item_id>
Tx id_ordlin
E </item_id>
E <commodity>
Tx item
E </commodity>
E </item>
E </order>
E </customer>
E </list>
Contents of the exported XML file (Join synth 1):
<?xml version="1.0" encoding="UTF-8"?>
<list>
<customer>
<surname>Smith</surname>
<order>
<order_id>0</order_id>
<item>
<item_id>0</item_id>
<commodity>paper</commodity>
</item>
</order>
</customer>
<customer>
<surname>Smith</surname>
<order>
<order_id>1</order_id>
<item>
<item_id>1</item_id>
<commodity>pencil</commodity>
</item>
</order>
</customer>
<customer>
<surname>Smith</surname>
<order>
<order_id>1</order_id>
<item>
<item_id>2</item_id>
<commodity>diskette</commodity>
</item>
</order>
</customer>
<customer>
<surname>White</surname>
<order>
<order_id>2</order_id>
<item>
<item_id>3</item_id>
<commodity>toner</commodity>
</item>
</order>
</customer>
DAD that will suppress identical values (stored as Join synth 2):
...
E <order> Multi_occurrence
...
E <item> Multi_occurrence
...
Contents of the exported XML file (Join synth 2):
<?xml version="1.0" encoding="UTF-8"?>
<list>
<customer>
<surname>Smith</surname>
<order>
<order_id>0</order_id>
<item>
<item_id>0</item_id>
<commodity>paper</commodity>
</item>
</order>
<order>
<order_id>1</order_id>
<item>
<item_id>1</item_id>
<commodity>pencil</commodity>
</item>
<item>
<item_id>2</item_id>
<commodity>diskette</commodity>
</item>
</order>
</customer>
<customer>
<surname>White</surname>
<order>
<order_id>2</order_id>
<item>
<item_id>3</item_id>
<commodity>toner</commodity>
</item>
</order>
</customer>
</list>
If the id_ord order number from the XML export is removed (stored under the name Join synth 3), items from different orders of the same customer would merge. It would be impossible to tell the difference between items of different orders.
If the order number is not desired in the XML output, but items from different orders in different occurrences of the order element is needed, it will be necessary to check the Repeat on change of checkbox in the order element properties and specify the id_ord column.
...
E <order> Multi_occurrence REPEAT ON CHANGE IN id_ord
...
The above DAD is stored under the name Join synth 4.
An analytic DAD contains table references to elements according to this rule:
Table T is stated in element E as a newly joined table so that each occurrence of element E corresponds with only one record in table T.Other tables in the nesting order are always joined to the previous table. Each join must be described by a key from a parent and child table. The record order of the parent table key must be specified.
The DAD for the three tables mentioned above (stored under the name Join analyth):
i XML TOP
E <list>
E <customer> TABLE Customers ORDER BY id_cust
E <surname>
Tx Customers.surname
E </surname>
E <order> JOIN TABLE Orders ON Orders.id_ord=Customers.id_cust ORDER BY id_ord
E <order_id>
Tx Orders.id_ord
E </order_id>
E <item> JOIN TABLE OrderLines ON OrderLines.id_ord=Orders.id_ord ORDER BY id_ordlin
E <item_id>
Tx OrderLines.id_ordlin
E </item_id>
E <commodity>
Tx OrderLines.item
E </commodity>
E </item>
E </order>
E </customer>
E </list>
The Customers, Orders and OrderLines tables are joined one-to-one in the customer, order and item elements.
All column names must be prefixed by a table name, since different tables may have the same column names.
Contents of the exported XML file (Join analyth):
<?xml version="1.0" encoding="UTF-8"?>
<list>
<customer>
<surname>Smith</surname>
<order>
<order_id>0</order_id>
<item>
<item_id>0</item_id>
<comodity>paper</comodity>
</item>
</order>
<order>
<order_id>1</order_id>
<item>
<item_id>1</item_id>
<comodity>pencil</comodity>
</item>
<item>
<item_id>2</item_id>
<comodity>diskette</comodity>
</item>
</order>
</customer>
<customer>
<surname>White</surname>
<order>
<order_id>2</order_id>
<item>
<item_id>3</item_id>
<comodity>toner</comodity>
</item>
</order>
</customer>
<customer>
<surname>Black</surname>
</customer>
</list>
Customers that do not have any orders are present in the file as well (customer Black). It is obvious that an OUTER JOIN is performed when joining the tables.
You may import an XML file to database tables using the same DAD. Let us assume that the DAD containing all columns will be used for export and import (some columns are omitted in the example). The values that bind the record relation (id_cust and id_ord) will be written to all tables during import to renew the dependencies between the records.
The following must be specified in the description of the table join:
Separate the column names in the list with commas. Both lists must have the same column count and the corresponding columns must be of the same type. If any of the column names contains a comma, it must be enclosed in grave accent characters.
If one table is used multiple times in the DAD, it is necessary to distinguish each individual occurrence.
An alias will allow each occurrence to be unique. The alias will be used for all references to this table instead of the original table name. When you assign a different alias for each occurrence in the DAD, references can be easily distinguished.
List of topics:
| Suppressing Identical Values before Repeating Elements | | XML Support | Selecting Records for Output to XML Using Variables | |