|
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.
|
See Also
Example of the Use of XML SQLExport
XML Output Description
When to use XML Export or XML SQLExport
|
