www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

Virtuoso Functions Guide

Administration
Aggregate Functions
Array Manipulation
BPEL APIs
Backup
Compression
Cursor
Date & Time Manipulation
Debug
Dictionary Manipulation
Encoding & Decoding
File Manipulation
Free Text
Hashing / Cryptographic
LDAP
Locale
Mail
Miscellaneous
Number
Phrases
RDF data
Remote SQL Data Source
Replication
SOAP
SQL
String
Transaction
Type Mapping
UDDI
User Defined Types & The CLR
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web & Internet
XML
xmlagg
xmlattributes
xmladdattribute
xmlappendchildren
xmlconcat
xmlelement
xmlforest
xmlinsertafter
xmlinsertbefore
xmlreplace
xmltype.xmltype
xmltype.createnonsch...
xmltype.createschema...
xmltype.createxml
xmltype.existsnode
xmltype.extract
xmltype.getclobval
xmltype.getnamespace
xmltype.getnumval
xmltype.getrooteleme...
xmltype.getschemaurl
xmltype.getstringval
xmltype.isfragment
xmltype.isschemabase...
xmltype.isschemavali...
xmltype.isschemavali...
xmltype.schemavalida...
xmltype.setschemaval...
xmltype.toobject
xmltype.transform
xmlupdate
xper navigation
createxml
isentity
serialize_to_utf8_xm...
tidy_html
tidy_list_errors
updatexml
xml_add_system_path
xml_auto
xml_auto_dtd
xml_auto_schema
xml_create_tables_fr...
xml_cut
xml_doc_output_optio...
xml_get_system_paths
xml_load_mapping_sch...
xml_load_schema_decl
xml_namespace_scope
xml_persistent
xml_set_ns_decl
xml_template
xml_tree
xml_tree_doc
xml_tree_doc_media_t...
xml_uri_get
xml_validate_dtd
xml_validate_schema
xml_view_dtd
xml_view_schema
xmlsql_update
xpath_eval
xper_cut
xper_doc
xper_locate_words
xpf_extension
xpf_extension_remove
xquery_eval
xslt
xslt_format_number
xslt_sheet
xslt_stale
xte_head
xte_node
xte_node_from_nodebl...
xte_nodebld_acc
xte_nodebld_final
xte_nodebld_init
xtree_doc
XPATH & XQUERY

Functions Index

xml_auto

prepares and executes given SQL for XML string output
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
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
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));
}
See Also

xml_auto_schema(),xml_auto_dtd()