TSToXML function

The TSToXML function returns an XML representation of a time series.

Syntax

TSToXML(doctype    lvarchar,
        id         lvarchar,
        ts         timeseries,
        output_max integer default 0)
returns lvarchar;

TSToXML(doctype    lvarchar,
        id         lvarchar,
        ts         timeseries)
returns lvarchar;

doctype

The name of the topmost XML element.

id

The primary key value in the time series table that uniquely identifies the time series.

ts

The name of the TimeSeries subtype.

output_max

The maximum size, in bytes, of the XML output. If the parameter is absent, the default value is 32 768. The following table describes the results for each possible value of the output_max parameter.

Value	Result
no value	32 768 bytes
negative integer	2³²-1 bytes
1 through 4096	4096 bytes
4096 through 2³²-1	the specified number of bytes

Description

Use the TSToXML function to provide a standard representation for information exchange in XML format for small amounts of data.

The top-level tag in the XML output is the first argument to the TSToXML function.

The id tag must uniquely identify the time series and refer the XML output to the row on which it is based.

The AllData tag indicates whether all the data was returned or the data was truncated because it exceeded the size set by the output_max parameter.

The remaining XML tags represent the TimeSeries subtype and its columns, including the time stamp.

The special characters <, >, &, ', and " are replaced by their XML predefined entities.

Returns

The specified time series in XML format, up to the size set by the output_max parameter. The AllData tag indicates whether all the data was returned (1) or whether the data was truncated (0).

Example

The following query selects the time series data for one hour by using the Clip function from the TimeSeries subtype named actual to return in XML format:

SELECT TSToXML('meterdata', esi_id,
       Clip(actual, '2010-09-08 12:00:00'::datetime year to second,
                    '2010-09-08 13:00:00'::datetime year to second ) )
FROM ts_data
WHERE esi_id = '2250561334';

The following XML data is returned:

<meterdata>
  <id>2250561334</id>
  <AllData>1</AllData>
  <meter_data>
    <tstamp>2010-09-08 12:15:00.00000</tstamp>
    <value>0.9170000000</value>
  </meter_data>
  <meter_data>
    <tstamp>2010-09-08 12:15:00.00000</tstamp>
    <value>0.4610000000</value>
  </meter_data>
  <meter_data>
    <tstamp>2010-09-08 12:15:00.00000</tstamp>
    <value>4.1570000000</value>
  </meter_data>
  <meter_data>
    <tstamp>2010-09-08 12:15:00.00000</tstamp>
    <value>6.3280000000</value>
  </meter_data>
  <meter_data>
    <tstamp>2010-09-08 12:15:00.00000</tstamp>
    <value>2.6690000000</value>
  </meter_data>
</meterdata>

The name of the TimeSeries subtype is meter_data and its columns are tstamp and value.

The value of 1 in the AllData tag indicates that for this example, all data was returned.