XML.SQLStatementExport

Previous Next

Exports data to the Meta-Rows-Fields document format on the basis of a SELECT statement.

This export method is the same as XML.SQLExport, except that the entire SELECT statement is passed as a variable element, which allows you to build or store a SELECT statement dynamically and then pass it to the export command.

In the same way as XML.SQLExport, this export method has the drawback that the result is NOT re-importable with XML.Import().

In the same way as XML.SQLExport, this export method has the advantage that it can deal with compound column expressions provided they are aliased. Also, the result contains metadata information. In summary, the result contains data but also information about how this data was queried.

Syntax

INVOKE     XML.SqlStatementExport WITH
SELECT     sql-statement

 

sql-statement  ::=

 

  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 1

INVOKE     XML.SQLStatementExport WITH
SELECT     'select ''yes'' UseIOFormats, * from employee'

Example 2

INVOKE     XML.SQLStatementExport WITH
SELECT     sql-statement
FROM       my-report-table

Example 3

The result of this call:

INVOKE     XML.SQLStatementExport WITH
SELECT     'select concat( concat( t.destination, '' '' ), t.tour_type ) tour_label, tp.num_days 
            from tour_programme tp, tour t 
            relate tp "IS FOLLOWED BY" t 

            where t.destination = ''AUSTRALIA'' 

            group by t.destination, t.tour_type, tp.num_days'

is this Meta-Rows-Fields document:

<SQLXMLExport>
  <Meta>
    <OutputExpressions>
      <Expression alias="tour_label">CONCAT(CONCAT(t.DESTINATION, &apos; &apos;), t.TOUR_TYPE)</Expression>
      <Column tableName="TOUR_PROGRAMME" tableAlias="tp" name="NUM_DAYS" alias="NUM_DAYS" objectNamePlural="Tour Programmes" prompt="Number of Days"/>
    </OutputExpressions>
  </Meta>
  <Rows>
    <Row>
      <Field alias="tour_label">AUSTRALIA ISLAND SUNTANNER</Field>
      <Field alias="NUM_DAYS">12</Field>
    </Row>
    <Row>
      <Field alias="tour_label">AUSTRALIA UP OR DOWN THE TRACK</Field>
      <Field alias="NUM_DAYS">10</Field>
    </Row>
  </Rows>
</SQLXMLExport>

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.