XMLTABLE expressions

Syntax

{ name [AS] alias } | { stream [AS] alias keep_clause [keep_clause] } xmltable_func

Components

name	The name of a stream or window.
alias	An alias for the stream or window.
stream	The name of a stream.
keep_clause	A window policy. See KEEP Clause for more information.
xmltable_func	An XMLTABLE function.

xmltable_func

XMLTABLE ( column ROWS xpath COLUMNS { expression AS out_column } [, ...] )

Components

column	The name of an XML column from the specified stream or window.
xpath	A constant string. Sybase CEP Engine applies this string to column using XPATH.
expression	An expression that contains no aggregators and evaluates to an unambiguous data type.
out_column	An output column.

Usage

Data sources listed in the FROM Clause: Comma-Separated Syntax and FROM Clause: Join Syntax variations of the FROM clause can include an XML table expression. This expression consists of an aliased data stream or aliased window and an XMLTABLE() function. The XMLTABLE() function receives rows from the data stream or window, and produces zero or more rows from every row received from the data stream or window.

The schema of the output from XMLTABLE() usually differs from the schema of the inbound rows. All other clauses in the query that reference the columns of the XMLTABLE() expression data source must reference the new schema produced by XMLTABLE(), not the schema of the associated data stream or window. Where an explicit reference to the data source is required, this data source must be referenced by the alias specified with the stream or window.

The XMLTABLE() function can reference the column names in the data stream or window to which it is linked. All unqualified column names within the XMLTABLE() are assumed to be to this data stream or window.

For every inbound row from the linked stream or window, the contents of the specified column are passed to XMLTABLE(). The constant string specified in XPATH string is then applied via XPATH once successively to each of the elements in the sequence passed to XMLTABLE(). The result of the XPATH operation on column is a sequence of elements, with each element generating one row, here referred to as CurrentXML. Every CurrentXML is treated as an XPATH context node, within the element tree provided by the associated data stream or window: therefore, the path . points to the CurrentXML node.

The list of expressions and output column names under COLUMNS specifies how each CurrentXML should be generated. Every CurrentXML is processed by the list of expressions and outputs into the corresponding column names. The output schema is generated from this list of expressions and column names, therefore, the data type of the column must be clearly identifiable from the expression. The following should be kept in mind in creating unambiguously typed output:

Constants output as the default data type for the constant.
The XML data type is permissible in the output.
The XMLEXTRACT() function implicitly returns an XML data type (or NULL).
The XMLEXTRACTVALUE() function implicitly returns a STRING data type (or NULL).
If the desired column data type is other than XML or STRING, an explicit data conversion function must be used.

Expression can contain column references from the linked data stream or window and can contain the XMLEXISTS(), XMLEXTRACT(), and XMLEXTRACTVALUE() functions, which operate on CurrentXML.

The variants of XMLEXISTS(), XMLEXTRACT(), and XMLEXTRACTVALUE() used with XMLTABLE() take only one argument: XPATH string. The implicit first argument in each of these functions is provided by CurrentXML and the functions operate exactly as their two-argument counterparts. When XMLEXTRACT() or XMLEXTRACTVALUE() are used with XMLTABLE() the functions copy only the context node contained in CurrentXML out of the element tree provided by the associated data stream or window.

Example

In the following example, stream Inventory has an XML column called inv, which contains invItem elements. These elements are first found with XPATH, using '//invItem' and a row is constructed for each invItem. The first column, called SKU, has a data type of XML. The second column, called Num, requires STRING to INTEGER conversion:

INSERT INTO ItemStream
SELECT L.SKU, L.Num
FROM Inventory AS L XMLTABLE(inv
   ROWS '//invItem'
   COLUMNS 
      XMLEXTRACT('SKU') AS SKU,
      TO_INTEGER(XMLEXTRACTVALUE ('NUM')) AS Num);