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