XML.SQLExport

Generates XML output based on the result of a SELECT statement. This method is slightly more complex than the XML.Export method, but it overcomes the following limitations of XML.Export:

1.	With XML.Export, only data from a single table may be exported as XML.

2.	With XML.Export, only 'real' columns can be exported. Calculated columns cannot be exported.

3.	With XML.Export, using Object Name Plural and Table Name as element names, and using Column Name as attribute name can cause problems when characters that are special for XML appear in Object Name Plural, Table Name or Column Name.

Syntax

INVOKE      XML.SQLExport WITH
SELECT      value    parameter-name
,           ...
,           expression   alias
,           ...
FROM        ...
WHERE       ...
GROUP BY    ...
ORDER BY    ...

In the SELECT output list, value parameter-name pairs and expressions may appear in any order. For possible parameter-names see the Parameters section later in this help topic.

Example

INVOKE    XML.SQLExport WITH
SELECT    'yes' UseIOFormats
,         *
FROM      tour

Unique aliases

Each expression in the SELECT output list must have a unique alias. Unique aliases are required to provide the link between the data and the metadata within the XML document.

If the expression does not simply map to a column but is composite, eg. by involving a SQL function, multiple column references, a string operator or a mathematical operation, then you must supply an alias.

If the expression simply maps to a column and you do not supply an alias, the Rules Engine will use the column name as an alias.

If you use multiple occurrences of the same column name in your SELECT output list, the Rules Engine will generate number suffixes to distinguish between these occurrences:

Statement	Generated unique aliases
SELECT id, id, id FROM emp	ID, ID_2, ID_3
SELECT 'Application' id, id FROM emp	ID, ID_2

If you use different columns with identical column names from different table instances, the Rules Engine will use the table name as a prefix to make the distinction at the alias level. In the case of two instance of the same table, it is ultimately the suffix that ensures alias uniqueness:

Statement	Generated unique aliases
SELECT e.id, d.id FROM emp e, dept d	EMP_ID, DEPT_ID
SELECT t1.id, t2.id, FROM tour t1, tour t2	TOUR_ID, TOUR_ID_2

If multiple SELECT clauses are connected by one or more set operators ( UNION, MINUS, INTERSECT), the first SELECT statement after the INVOKE keyword provides the alias.

Parameters

All parameters are optional, and the parameter names and values are not case-sensitive.

value	parameter-name	Description
{ yes \| no }	AppendToFile	Value is a string and must be quoted, as in the example earlier in this help topic. If you specify 'yes' for this parameter, the file is appended to. Otherwise, the file is overwritten.
{ yes \| no }	UseIOFormats	Value is a string and must be quoted, as in the example earlier in this help topic. If you specify 'yes' for this parameter, column values in the resulting XML output get the IO format of the corresponding domain. Otherwise, they are written as they are retrieved from the database.
output-file-name	WriteOutputTo	Specifies whether the output is to be written directly to a file. It is possible to use a file which has previously been opened using the USFile.Open method. To do this, the alias used in the USFile.Open method must be specified, preceded by 'USFile.' If no USFile alias is specified, the XML will be written directly to the file specified. If the file exists, it will be overwritten. To prevent this, use the 'Append' parameter. If specified, the method will return the file name.
{ yes \| no }	XmlDeclaration	Value is a string and must be quoted, as in the example earlier in this help topic. If you specify 'yes' for this parameter, the default Rules Engine encoding is returned in XML processing instructions. Otherwise, no encoding processing instruction is added to the returned XML document.