XML Data Bound to Variables and Constants

XML Binding to Database Columns

When describing the structure of an XML file, elements are added to the DAD and nested just like they should be nested in the XML file. You may also add any number of attributes to an element, along with a text description.

Binding a Database Column Value with an Attribute or Element

A column value in a database may be bound to either an attribute (<customerID="1">) or element (<ID>1</ID>). Use the context menu Insert element attribute or Insert element when you want to place a value in the XML file.

If you insert an attribute, you must define the name. This name does not need to be the same name of the column whose value is in the attribute.

In both cases, check the Associate with database column checkbox and select the name of the table and column whose value should appear in the attribute or element. If you want to use a column from a query result in a synthetic DAD, select "- base cursor -" instead of the table name.

You may combine the placement of column values in attributes and elements as you see fit.

Using Expressions when Exporting to XML

If you need to use expression values calculated from database values into an XML file, you must do the following:

Design a synthetic DAD.
Specify the expressions in the SELECT clause of the SQL query.

It is recommended to name these expressions using the AS clause, since it is easy to identify the values of these expressions in the values menu for XML output.

Transforming Values During a Transfer

The need to transform a value may arise when defining an XML transfer. It is usually required when the XML format is fixed and the result cannot be achieved using expressions or the XML format does not directly corresponding with the table structure (when transferring from XML to the database).

Transformation is performed using an SQL function created in the same application. These functions must have the following header:

FUNCTION function_name (IN InputValue NCHAR(2045)) RETURNS NCHAR(2045);

The function header is common for all types (unusable for BINARY type and variable-length types). The value is taken from a table, transformed to NCHAR, passed to the function and the result is written to the XML file in the selected coding when transferred to XML. The string value from the XML document is used when transferring from XML to the database. It is passed to the function and the result is written to the appropriate table.

The transformation function is specified in the XML text properties and XML attribute properties dialog. The Translation button opens a new dialog to select an already existing function (separate for both transfer directions). If the function does not exist yet, click the Create new button to automatically open the editor with the transformation function.

You may not use transformation functions on Windows 98, since UNICODE characters are used in the function parameters.

Insert or Update Data when Transferring to a Database

You may specify (analytic DAD = transferring data to a database) if repeatable groups of values from XML will be inserted as new records (INSERT, default option) or existing records will be overwritten (UPDATE).

It is necessary to have a UNIQUE key in the table and the XML file must contain these values (one or more columns) in order to overwrite existing values. You must specify key values by checking the Is a key of the record to update checkbox on the XML text properties and XML attribute properties window.

Merging Values into Elements

The XML file may have a structure that is more complex than the database. Values of some columns that are bound, may be inserted into a special element.

These elements may be defined as needed. They only represent data in the XML file, not the database.

Example

Customers table with the following contents:

ID_CUST	FIRST_NAME	SURNAME	REGISTER_DATE
0	John	Smith	25.2.1997
1	Peter	White	25.2.1997
2	Charles	Black	3.12.2002

Export the table data to XML with these requirements:

The root element list will contain one element customer for each table record.
The customer element will have the ID attribute containing the ID_CUST value from the appropriate record.
The customer element will contain the person element for customer identification and registered element for date of registration.
The person element will contain the name and surname elements.

The export will be prepared as follows:

Create a new DAD and close the wizard.
Rename the root element to list.
Execute the Insert subelement of element <list> command from the <list> item context menu.
Specify the customer name in the dialog for the new element, check the Join a new table here checkbox and select the table name Customers.
Execute the Insert attribute of element <customer> command from the <customer> item context menu.
Specify the name ID in the new attribute dialog, select the Customers table and id_cust column for the value.
Using the context menu of the <customer> item, insert the person and registered subelements, then enter the element names in the dialog.
Using the context menu of the <person> item, insert the name and surname subelements, then enter the element names in the dialog. If you place an element incorrectly, move it using drag-and-drop or the clipboard.
Insert text into the name, surname and registered elements, select the Customers table in the parameters, and the corresponding columns first_name, surname and register_date.

The complete DAD design looks like this (only the tree contents are displayed here, not the entire tree) - E means a element, Tx means a text in a element, At means a attribute of a element):

i XML Top 
E <list>
  E <customer> TABLE Customers
    A ID="Customers.id_cust"
    E <person>
      E <name>
        Tx Customers.first_name
      E </name>
      E <surname>
        Tx Customers.surname
      E </surname>
    E </person>
    E <registered>
      Tx Customers.register_date
    E </registered>
  E </customer>
E </list>

Exported XML document:

<?xml version="1.0" encoding="UTF-8"?>
<list>
 <customer ID="0">
  <person>
   <name>John</name>
   <surname>Smith</surname>
  </person>
  <registered>25.2.1997</registered>
 </customer>
 <customer ID="1">
  <person>
   <name>Peter</name>
   <surname>White</surname>
  </person>
  <registered>25.2.1997</registered>
 </customer>
 <customer ID="2">
  <person>
   <name>Charles</name>
   <surname>Black</surname>
  </person>
  <registered>3.12.2002</registered>
 </customer>
 </customer>
 <customer ID="3">
...
 </customer>
</list>

A quicker way to create a simple DAD is to use automatic DAD generation for the Customers table. Rename the elements, create the new element person and move the name and surname elements one level lower to the person element using drag-and-drop.

XML Data Bound to Application Data

XML Data Bound to Variables and Constants