The XML SQLExport Method

Previous Next

See Also

The standard XML.Export facility is easy to use for simple export/import work and it results in a clear and compact XML document. The solution does, however have limitations:

1. Only data from one table can be exported as XML. To export data from multiple tables a view needs to be defined in USoft.
2. Only 'real' columns can be exported. Calculated columns cannot be exported.
3. Allowing Object Name Plural, Table Name as elements and Column Name as attribute name can cause problems when using XML special characters in Object Name Plural, Table Name and Column Name.

The XML.SQLExport method overcomes these limitations.

SYNTAX

INVOKE XML.SQLExport

WITH

SELECT    [<value> <param>[, <value> <param> ...]]

        <expression>[,expression]

       [,<value> <param>[, <value> <param> ...]]

FROM <table list>

WHERE <where clause>

GROUP BY <group by list>

ORDER BY <order by list>

etc.

In addition to SELECT statements, set operators (UNION/MINUS/INTERSECT) can also be used.

NOTE

Value/Parameter pairs and expressions can appear in any order within the SELECT statement.

EXAMPLE

INVOKE XML.SQLExport WITH SELECT 'yes' useioformats , * FROM t2_

EXPRESSIONS

The possible values for <expression> are:

<column>, OLD(<column>), *, OLD(*)

PARAMETERS

All parameters are optional, and the parameter names and values are not case-sensitive. See the table below for more information.

Parameter

Allowed Values

Description

UseIOFormats

No, Yes

Specifies whether column values in the resulting XML output get the IO format of the corresponding domain or not.

If not they are written as they are retrieved from the database.

WriteOutputTo

Output file name

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.

AppendToFile

No, Yes

This parameter is used with the "WriteOutputTo" parameter. You can use it to specifiy whether the file is to be overwritten, or appended to.

XmlDeclaration

No, Yes

Specifies whether the default Rules Engine encoding is to be returned in XML processing instructions. Otherwise no encoding processing instruction is added to the returned XML document.

 

Related Topics

Example of the Use of XML SQLExport

XML Output Description

Notes on Aliases in XML SQLExport

When to use XML Export or XML SQLExport