# 15. Web Services

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Web Services
SOAP
WSDL
WS-Security (WSS) Support in Virtuoso SOAP Server
Web Services Routing Protocol (WS-Routing)
Web Services Reliable Messaging Protocol (WS-ReliableMessaging)
Web Services Trust Protocol (WS-Trust)
XML for Analysis Provider
XML-RPC support
SyncML
UDDI
Exposing Persistent Stored Modules as Web Services
Testing Web Published Web Services
BPEL Reference
XSQL
XSQL Syntax XSQL Directives

## 15.14. XSQL

XSQL is an XML-based format for describing simple stored procedures that can parse XML data, query or update database tables and compose XML output. Both input and output XMLs of such procedures are usually standard three-level documents: a top-level ROWSET element contains some number of ROW elements and every ROW contains one element per database field. XSQL lets the application developer avoid writing routine code for parsing and composing such documents: the server translates a short and self-evident XSQL description into a relatively long Virtuoso/PL procedure.

XSQL pages are usually executed from XSLT stylesheets by calling processXSQL() XPATH function. When the page is executed, it can access an XML entity that is context entity of the processXSQL() call. This entity is used inside the page as an implicit parameter called "context XML".

An XSQL document that describes one procedure is called "XSQL page". A page consists of small directives. Every directive is written as a single XML element from namespace "urn:oracle-xsql" (the typical namespace prefix is "xsql"). Every directive describes one standard operation. The resulting Virtuoso/PL procedure will execute all directives in turn. Directives are of four sorts:

- Parameter assignments create and initialize local variables ("page parameters") that can be used in the rest of page.
- Data modification requests can insert, delete or update data in database tables.
- XML generators can query database or page parameters and produce XML fragments. These fragments form the resulting XML that is returned by the procedure.
- DML directives let the author to put arbitrary Virtuoso/PL code in the procedure.

All directives are children of one op-level element called 'xsql:page'. This element can have any number of attributes but no one attribute is used by Virtuoso. These attributes may be used by specialized XSQL editors and standalone XSQL processors that should establish a database connection to read and write data so store connection details as attributes of 'xsql:page'.

The XSQL development cycle consists of editing '.xsql' resources in the file system or Virtuoso DAV. The editing can take place using a regular text editor or a supporting XML editor or some specialized third-party XSQL tool.

### 15.14.1. XSQL Syntax

Properties of each XSQL directives are specified by XML attributes. These attributes are of different types, mostly SQL expressions, calculateable strings, SQL names and lists of names (e.g., name of a database table or a list of columns of a table).

In some cases, SQL expressions are long and it is not convenient to place them into attribute values. Such expressions are written as text content of the XSQL directive element.

SQL expressions

XSQL allows slightly extended syntax of SQL expressions. If X is a name of page parameter made by xsql:set-page-param then special notation "{@X}" stands for the reference to the parameter value. This notation can be used in any place where a variable name is acceptable. In addition, this notation can be used inside string literals, such a literal is translated into a string contatenation expression; e.g., 'text-head{@X}text-middle{@Y}text-tail' is converted into concat ('text-head', cast ({@X} as varchar), 'text-middle', cast ({@Y} as varchar), 'text-tail').

Calculateable strings

Some attribute values are strings like resource URIs or XML names to be used in the output generated by a page. They are usually written 'as is' but they can use "{@X}" notation to insert the value of page parameter into the resulting string. The actual value of a calculateable string is compiled only once even if it appears many times in the XML output of the page.

Names of SQL columns

Some attributes are not arbitrary SQL expressions but only SQL column names. These names are written 'as is'. Only unqualified names are allowed, not in form 'table.column'. No "{@X}" is allowed for obvious reasons. If SQL name is case-sensitive or contains nonalphabetical characters then the name should be enclosed in double quotes. For readability, use single quotes to surround attribute value to not mix them with possible double quotes used in SQL name. Whitespace characters are not allowed in these names because these will not make proper names of XML elements.

Names of SQL tables

SQL table names are also written 'as is', with the same rules for double quotes around case-sensitive parts of the name. Both qualified and unqualified names are allowed.

Lists of names of SQL columns

When the value of attribute lists one or more column names, white space characters or commas delimit column names. The list should be space-delimited like 'COL1 COL2 COL3' or comma-delimited like 'COL1, COL2, COL3' but not a mix of them like 'COL1, COL2 COL3'.

### 15.14.2. XSQL Directives

#### 15.14.2.1. xsql:delete-request

Deletes the rows listed in the context XML.

    <xsql:delete-request
      table="table_name"
      key-columns="column_list"
      [ transform="calculateable_URI_string" ]
    />

If 'transform' URI is specified then it is used as a name of XSLT stylesheet that is applied to the context XML before the rest of processing. The result of that stylesheet should be in standard ROWSET/ROW form. The result may also contain elements with other names but they will be silently ignored.

After this optional XSLT transformation, the XSQL procedure gets all ROW elements in all top-level ROWSET elements of the source XML. For each such element it parses all subelements whose names match column names listed in the 'key-columns' attribute. It is legal to have a ROW element that does not contain an element that matches a particular column; the missing element is treated as a database NULL. If a subelement of ROW has attribute 'NULL' equal to 'Y' then it is also treated as database NULL.

When required subelements of ROW are parsed, the procedure deletes all rows from the specified table that have all specified column values equal to values specified by ROW subelements. When the delete operation is complete, the procedure parses all subelements of the next ROW element in queue and so on.

    <xsql:delete-request table='"Demo"."demo"."Customers"' key-columns='"CustomerID"'/>

#### 15.14.2.2. xsql:dml

Executes an arbitrary fragment of Virtuoso/PL code.

    <xsql:dml>
    One or more Virtuoso/PL statements, {@X} syntax is allowed.
    </xsql:dml>

The XSQL procedure will contain the text of the directive 'as is'; the only change is that {@X} notation is replaced with appropriate Virtuoso/PL variables. Note that the use of <![CDATA[...]]> XML syntax is very convenient here.

    <xsql:set-page-param name="X" value="2"/>
    <xsql:set-page-param name="Y" value="2"/>
    <xsql:dml><![CDATA[
    -- This will simply print the string on server's console:
    dbg_obj_print ('Hello World\n');
    -- This will compose a string '2 * 2 =' and print on console:
    dbg_obj_print ('{@X} * {@Y} = ');
    -- This will calculate and print a well-known product.
    -- Note that attempt to write {@X * @Y} or {@X * Y} results in a syntax error.
    dbg_obj_print ({@X} * {@Y}, '\n');
    ]]>
    </xsql:dml>

#### 15.14.2.3. xsql:include-owa

This Oracle-specific directive is not implemented.

#### 15.14.2.4. xsql:include-param

Puts the value of the spacefied page parameter into the resulting XML of the page.

    <xsql:include-param name="page_param_name"/>

This writes the value of the specified page parameter as an element whose name is made from the name of parameter. NULL value results in an empty element that has attribute named 'NULL' with value 'Y'.

Note that XML elements ROWSET and ROW have special use in XSQL pages. The use of parameter names ROWSET and ROW is legal but may cause undesired effects.

    <xsql:set-page-param name="an-int" value="2"/>
    <xsql:set-page-param name="an-xml" value="xtree_doc('<sample>text</sample>')"/>
    <xsql:set-page-param name="a-null" value="NULL"/>
    <!-- This will form an element <an-int>2</an-int> -->
    <xsql:include-param name="an-int"/>
    <!-- This will form an element <an-xml><sample>text</sample></an-xml> -->
    <xsql:include-param name="an-xml"/>
    <!-- This will form an element <a-null NULL="Y"/> -->
    <xsql:include-param name="a-null"/>

#### 15.14.2.5. xsql:include-request-params

This directive is not implemented in this version.

#### 15.14.2.6. xsql:include-xml

This directive is not implemented in this version.

#### 15.14.2.7. xsql:include-xsql

This directive is not implemented in this version.

#### 15.14.2.8. xsql:insert-param

Inserts rows listed in XML that is stored in page parameter.

    <xsql:insert-param
      name="parameter_XML_name"
      table="table_name"
      [ mode="enum" (could be "into", "soft" or "replacing", default is "into") ]
      [ columns="column_list" ]
      [ date-format="string" (allowed but ignored) ]
      [ transform="calculateable_URI_string" ]
    />

The directive inserts into a table all data rows listed in the value of a page parameter that is named by 'name' attribute. The destination table is specified by 'table' attribute.

If 'transform' URI is specified then it is used as a name of XSLT stylesheet that is applied to the source data before the rest of processing. The result of that stylesheet should be in standard ROWSET/ROW form. The result may also contain redundant elements but they will be silently ignored.

After this optional XSLT transformation, the XSQL procedure gets all ROW elements in all top-level ROWSET elements of the source. For each such element it parses all subelements whose names match column names listed in the 'columns' attribute; if the 'columns' attribute is not specified then all column names from the destination table are used. It is legal to have a ROW element that does not contain an element that matches a particular column; the missing element is treated as a database NULL. If a subelement of ROW has attribute 'NULL' equal to 'Y' then it is also treated as database NULL.

When required subelements of ROW are parsed, the procedure adds a row to the table. It executes INSERT INTO, INSERT SOFT or INSERT REPLACING statement depending on the value of 'mode' attribute. When the insert operation is complete, the procedure parses all subelements of the next ROW element in queue and so on.

    <xsql:insert-param name="customer-details" table='"Demo"."demo"."Customers"' />

#### 15.14.2.9. xsql:insert-request

Inserts the rows listed in context XML.

    <xsql:insert-request
      table="table_name"
      [ mode="enum" (could be "into", "soft" or "replacing", default is "into") ]
      [ columns="column_list" ]
      [ date-format="string" (allowed but ignored) ]
      [ transform="calculateable_URI_string" ]
    />

The directive inserts into a table all data rows listed in context XML. The destination table is specified by 'table' attribute.

If 'transform' URI is specified then it is used as a name of XSLT stylesheet that is applied to the context XML before the rest of processing. The result of that stylesheet should be in standard ROWSET/ROW form. The result may also contain redundant elements but they will be silently ignored.

After this optional XSLT transformation, the XSQL procedure gets all ROW elements in all top-level ROWSET elements of the source. For each such element it parses all subelements whose names match column names listed in the 'columns' attribute; if the 'columns' attribute is not specified then all column names from the destination table are used. It is legal to have a ROW element that does not contain an element that matches a particular column; the missing element is treated as a database NULL. If a subelement of ROW has attribute 'NULL' equal to 'Y' then it is also treated as database NULL.

When required subelements of ROW are parsed, the procedure adds a row to the table. It executes INSERT INTO, INSERT SOFT or INSERT REPLACING statement depending on the value of 'mode' attribute. When the insert operation is complete, the procedure parses all subelements of the next ROW element in queue and so on.

    <xsql:insert-request table='"Demo"."demo"."Customers"' columns='"CustomerID", "Phone", "Fax"'/>

#### 15.14.2.10. xsql:query

This executes an SELECT statement and writes its result set into the resulting XML in some sort of ROWSET/ROW format.

    <xsql:query
      [ fetch-size="integer" (allowed but ignored) ]
      [ id-attribute="calculateable_XML_NAME_string" ]
      [ id-attribute-column="SQL_column_name" ]
      [ max-rows="integer_SQL_expn" (default is no limit) ]
      [ null-indicator="boolean" (default value is "no") ]
      [ row-element="calculateable_XML_NAME_string" (default value is "ROW") ]
      [ rowset-element="string" (default value is "ROWSET") ]
      [ skip-rows="integer_SQL_expn" (default is no skip) ]
      [ tag-case="enum" (could be "lower" or "upper", default is "lower") ]
    >
    Text of SELECT statement, {@X} syntax is allowed.
    </xsql:query>

When no attributes is specified, the directive executes the SELECT statement and composes an XML fragment that consists of ROWSET element that have one ROW child element per row of the result set of the executed statement. Every ROW has one child element per result set column. The name of each element is made by converting column name to lowercase and element values are serializations of result set fields. The procedure does not create elements for fields with NULL values so an element with no text inside means empty string value but the totally missing element means NULL. In addition, ROW element have so-called "id attribute" whose name is 'num' and value is a number of the row in the result set.

If attribute 'null-indicator' is equal to '1' or 'yes' then elements are created for both non-NULL and NULL field values. Unlike elements that represent empty strings, element that represent NULL will have an attribute with name 'NULL' and value 'Y'.

Attributes 'id-attribute' and 'id-attribute-column' configures the composing of "id attribute". 'id-attribute-column' instructs to use the value specified SQL column as a value of attribute, 'id-attribute' can specify an attribute name other than default 'num'.

Attribute 'tag-case' specifies the character case of elements for columns. This does not affect names for ROW and ROWSET elements.

Element names ROW and ROWSET can be changed to whatever else by specifying attributes 'row-element' and 'rowset-element'. Either or both of these elements can be omitted at all by specifying empty string value for appropriate attributes; this will prevent the result set from proper parsing by some standard tools but may be convenient for special purposes. Note that If 'row-element' is empty string and ROW should not be created then attributes 'id-attribute' and 'id-attribute-column' should not be used.

Attributes 'max-rows' and 'skip-rows' adds TOP N and SKIP N clauses to the specified SELECT statement. They are supported mostly for compatibility. Attribute 'fetch-size' is ignored at all.

Note that Oracle allows the xsql:query to contain any code that produces a result set whereas Virtuoso allows only SELECT statement.

    <xsql:set-page-param name="usermask" value="'%DAV%'"/>
    <xsql:query>
    -- This will produce the following XML fragment:
    -- <ROWSET>
    --   <ROW num="1"><u_id>3</u_id><u_name>administrators</u_name>
    --      <u_full_name>WebDAV Administrators</u_full_name><ROW>
    --   <ROW num="2"><u_id>3</u_id><u_name>dav</u_name>
    --      <u_full_name>WebDAV System Administrator</u_full_name><ROW>
    -- </ROWSET>
    SELECT U_ID, U_NAME, U_FULL_NAME from SYS_USERS where U_FULL_NAME like {@usermask}
    </xsql:query>

#### 15.14.2.11. xsql:ref-cursor-function

This directive is not implemented in this version of Virtuoso Server.

#### 15.14.2.12. xsql:set-cookie

This Oracle-specific directive is not implemented.

#### 15.14.2.13. xsql:set-page-param

Declares a page parameter and assigns a value to it.

    <xsql:set-page-param
      name="XML_name"
      [ ignore-empty-value="boolean" ]
      [ value="SQL_expression" ]
      [ xpath="XPATH_expression" ]
    >
    SQL expression, {@X} syntax is allowed, but only
    for names that were declared above the current one.
    </xsql:set-page-param>

The declaration should contain either 'value' attribute or 'xpath' attribute or a SQL expression as a text inside element, but not two or three of them simultaneously. The specified expression is calculated and the result is saved as a page variable with name specified by 'name' attribute. The resulting page variable can be used in other SQL expressions and calculateable strings of the page via '{@X}' notation described in XSQL Syntax subsection above.

If the directive uses 'xpath' attribute, the XPATH expression is applied to the context XML of the page. To apply an XPATH expression to some other XML entity, use xpath_eval() function in SQL expression specified by 'value' attribute or text inside the element.

If attribute 'ignore-empty-value' is set to 'yes' or '1' and the value of the calculated expression is an empty string then the page parameter is set to NULL. This may be convenient for handling default values that are passed to the page from HTML forms.

It is recommended to have names of parameters compatible with "XML 1.1 unqualified name" syntax. Hence, it is better to use minus sign instead of underscore and avoid using unusual characters like spaces. This become important when you use xsql:include-param because the name of create XML element will be equal to the name of parameter.

    <!-- string constant '%DAV%' is an SQL expression -->
    <xsql:set-page-param name="user-mask" value="'%DAV%'"/>
    <!-- SELECT statement is an expression too, but only when enclosed in parenthesis.
      Without parenthesis, it is an SQL operator but not an SQL expression. -->
    <xsql:set-page-param name="user-id">
    (select U_ID from SYS_USERS where U_FULL_NAME like {@user-mask})
    </xsql:set-page-param>
    <!-- This copies implicit page argument 'context XML'
      into plain page parameter 'my-context' -->
    <xsql:set-page-param name="my-context" xpath="." />

#### 15.14.2.14. xsql:set-session-param

This Oracle-specific directive is not implemented.

#### 15.14.2.15. xsql:set-stylesheet-param

This directive is not implemented.

#### 15.14.2.16. xsql:insert-request

Updates the rows listed in context XML.

    <xsql:update-request
      key-columns="column_list"
      table="table_name"
      [ columns="column_list" ]
      [ date-format="string" (allowed but ignored) ]
      [ transform="calculateable_URI_string" ]
    />

The directive updates a table by changing rows listed in the context XML. The destination table is specified by 'table' attribute.

If 'transform' URI is specified then it is used as a name of XSLT stylesheet that is applied to the context XML before the rest of processing. The result of that stylesheet should be in standard ROWSET/ROW form. The result may also contain redundant elements but they will be silently ignored.

After this optional XSLT transformation, the XSQL procedure gets all ROW elements in all top-level ROWSET elements of the source. For each such element it parses all subelements whose names match column names listed in the 'key-columns' attribute or in the 'columns' attribute; if the 'columns' attribute is not specified then it works as if all column names from the destination table are listed in 'columns'. It is legal to have a ROW element that does not contain an element that matches a particular column; the missing element is treated as a database NULL. If a subelement of ROW has attribute 'NULL' equal to 'Y' then it is also treated as database NULL.

When required subelements of ROW are parsed, the procedure updates all rows in the table that have all values of key columns equal to values listed in ROW. All these rows are updated by values from subelements of ROW. If 'columns' is specified then only named fields are updated; otherwise, the update operation changes all fields of a table.

When the update operation is complete, the procedure parses all subelements of the next ROW element in queue and so on.

    <xsql:insert-request table='"Demo"."demo"."Customers"' columns='"CustomerID", "Phone", "Fax"'/>

Previous BPEL Reference | Chapter Contents | Next Contents of Runtime Hosting