Name

xml_auto — prepares and executes given SQL for XML string output

Synopsis

xml_auto ( in sql_text varchar ,
  in params any ,
  in string_output any );
 

Description

This function prepares and executes the given SQL string, which should be a query expression with the FOR XML clause at the end of the last term. The query is passed the parameters from the params vector, which should have one element for each ? in the query text, values assigned from left to right. Consider the query: select a, b from table where a = ? and b = ?; then the params vector could reasonably be:

vector(1, 'myfilter')

.

The result set is converted to XML and appended to the string _output. If the string _output is omitted and the function executes in the context of a VSP page, the output is sent to the stream going to the user agent.

Parameters

sql_text

Valid SQL query using the FOR XML clause. Parameterized queries can be constructed using the question mark (?) to specify a parameter place-holder that will be replaced at run time with the appropriate value from the params vector.

params

Vector of parameters, one element per ? used in the query.

string_output

String variable or stream for receiving the result.

Return Types

If you omit the third parameter, this function will output to the context of the calling VSP page.

Errors

Table 24.130. Errors signalled by xml_auto

SQLState Error Code Error Text Description
42000 http output function outside of http context and no stream specified From an attempt to send the output directly to a non HTTP target such as ISQL.
42000 Column 1 of the result set of the select statement should be of INTEGER type when FOR XML EXPLICIT clause is used  
42000 Column 2 of the result set of the select statement should be of INTEGER type when FOR XML EXPLICIT clause is used  

Examples

Example 24.507. Producing XML from SQL

The procedure below takes an SQL string, evaluates it - converting to XML - and produces a result set where the XML text is returned as a varchar column. Parameters are not passed in this example for the sake of simplicity.

create procedure xmla (in q varchar)
{
  declare st any;
  st := string_output ();
  xml_auto (q, vector (), st);
  result_names (q);
  result (string_output_string (st));
}