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.129. 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));
}