XML.SQLExport output description

This help topic describes elements of an XML.SQLExport result document such as the following:

<SQLXMLExport>
    <Meta>
      <OutputExpressions>
        <Column tableName="TOUR" tableAlias="t1" name="DESTINATION" alias="Dest_Before_Connection" prompt="Tour Destination" />
        <Column tableName="TOUR" tableAlias="t2" name="DESTINATION" alias="Dest_After_Connection" prompt="Tour Destination" />
        <Expression alias="tour_title">t1.destination </Expression>
      </OutputExpressions>
    </Meta>
    <Rows>
      <Row>
      <Field Alias="a">1</Field>
      <Field Alias="b">1</Field>
      <Field Alias="decoded">b</Field>
      </Row>
      <Row>
        <Field Alias="a">4</Field>
        <Field Alias="b">2</Field>
        <Field Alias="decoded">c</Field>
      </Row>
    </Rows>
</SQLXMLExport>

<SQLXMLExport> is the the top-level element of each XML.SQLExport result document.

The <Meta> element is a required child element of the <SQLXMLExport> top-level element and contains only the required <OutputExpressions> child element. <OutputExpressions> contains, in turn, a list of child elements, each of which is either <Column> or <Expression>.

<Meta> is the only element by that name in the document. The same applies to <OutputExpressions>.

Each <Column> element represents a simple output expression in the SELECT list that corresponds to a database column, as opposed to a more complex or composite output expression involving a SQL function, a string operation or a mathematical computation (or several of these). A simple output expression may or may not have a column alias in the SELECT statement.

Composite output expressions are represented by an <Expression> element.

<Column> elements do not have content but they have the following attributes:

Attribute	Description
tableName	The name of the database table
tableAlias	The table alias of the database table
name	The name of the database column
alias	The unique alias of the output expression
prompt	The prompt associated with the column

tableAlias is the only attribute that is not necessarily included. It exists only if a table alias was used in the FROM clause.

The purpose of alias is to map information under the <Meta> element to information in the <Rows> element. The alias of each output expression must therefore be unique across the entire SQL statement. USoft attempts to disambiguate aliases automatically. Go to XML.SQLExport for details.

prompt is the Prompt that you defined for the column in USoft Definer. Typically, each column is given a Prompt, but USoft Definer does not enforce it. If no Prompt is available, USoft uses the column name (name).

Examples

Each <Expression> element represents an output expression in the SELECT list that is composite in the sense that it involves a literal expression, a SQL function, a string operation or a mathematical computation, or several of these. Such a composite expression must have a column alias.

Non-composite output expressions, that map directly to a database column, are represented by a <Column> element instead.

<Expression> elements have content and the following required attribute:

Attribute	Description
alias	The name of the alias of the output expression

The content of the <Expression> element is the SQL output expression itself, with column names expanded to the format

table-name.column-name

where table-name is the table alias if one is available, or otherwise equals the full database table name. Any characters that are special to XML are replaced by predefined entities, eg., a single quote in the SQL output expression is replaced by ' . For example:

<Expression alias="TourTitle">TOUR.DESTINATION || ' ' || TOUR.TOUR_TYPE</Expression>

XML.SQLExport can still export data if there is no table in the FROM clause of the SELECT statement. In that case the <OutputExpressions> element will not contain any <Column> elements, but only one or more <Expression> elements.

<Rows>

The <Rows> element is the only element by that name in the document. It is a sibling of <Meta> and contains a <Row> child for each record exported. If the result of the SELECT statement has n records then there will be n <Row> children.

The list of <Row> children is logically unordered, but if the SELECT statement has an ORDER BY clause, then this will determine the order in which the <Row> elements appear in the document.

If no records are retrieved, the document contains only the <SQLXMLExport> element, the <Meta> element and the <Rows> element. It will not contain any <Row> elements.

<Row>

Each <Row> element corresponds to a record queried by the SELECT statement. Each <Row> element is a child of the <Rows> element and contains a list of <Field> elements, one for each column value in the record.

<Field>

Each <Field> element corresponds to a column value in a retrieved record. The number of <Field> children of the <Row> element is equal to the number of expressions in the output list of the SELECT statement.

The <Field> elements within a <Row> element are logically unordered.

<Field> elements have content and the following required attribute:

Attribute	Description
alias	The name of the alias of the output expression

The purpose of alias is to map information in the <Field> element to information in the <Meta> element. The alias of each output expression must therefore be unique across the entire SQL statement. USoft attempts to disambiguate aliases automatically. Go to XML.SQLExport for details.

The content of a <Field> element is a result value that maps to an output expression in the SELECT list.