Updating Relational Data

Updating Relational Data

DataDirect XQuery supports updates to relational database tables from inside an XQuery by providing three built-in functions. These functions are:

ddtek:sql-insert

ddtek:sql-update

ddtek:sql-delete

The DataDirect XQuery relational update functionality is an extension of the XQuery Update Facility (XUF). To learn about DataDirect XQuery’s support for XUF and how to use XUF to update XML, see Chapter 5, Tutorial: The XQuery Update Facility.

ddtek:sql-insert

The ddtek:sql-insert built-in function inserts a single record in a database table.

The syntax is:
declare updating function ddtek:sql-insert( 
    table as xs:string, 
    column as xs:string, 
    value as item()*, 
    ...) external; 
where:

table is the database table in which to insert the record. The semantics of table are equivalent to those for fn:collection; see Specifying Relational Database Tables.

column is the column of the database table in which to insert a value.

value is the value to insert into the specified column.

column and value are a pair in a variable argument list. If column is specified without value, an error is raised. You can specify multiple values for this pair, as shown in the example.

The following example inserts a new record with three columns into the holdings table. The columns and their values are
userid=Minollo, stockticker=TIVO, and shares=200.
ddtek:sql-insert("holdings", "userid", "Minollo", "stockticker", "TIVO", 
  "shares", 200) 
Other examples can be found in the RDBMSUpdate example.

ddtek:sql-update

The ddtek:sql-update built-in function updates records in a database table.

The syntax is:
declare updating function ddtek:sql-update( 
    row as element()*, 
    column as xs:string, 
    value as item()*, 
    ...) external; 
where:

row identifies the records in the database table to update. Each item in the sequence must be a row element of the database table returned by a previous fn:collection call.

column is the column of the database table to update.

value is the new value for the specified column.

column and value are a pair in a variable argument list. If column is specified without value, an error is raised.

The following example updates a record in the holdings table – in particular, the record where the userid column equals Minollo and the stockticker column equals PRGS. In this record, the shares column is updated to 500.
ddtek:sql-update( 
  collection("holdings")/holdings[userid="Minollo" and stockticker="PRGS"], 
  "shares", 500) 
Other examples can be found in the RDBMSUpdate example.

ddtek:sql-delete

The ddtek:sql-delete built-in function deletes records in a database table.

The syntax is:
declare updating function ddtek:sql-delete( 
    row as element()*) external; 
where:

row identifies the records to be deleted. Each item in the sequence must be a row element of the database table returned by a previous fn:collection call.

The following example deletes all of the records in the holdings database table where the userid column equals Minollo.
ddtek:sql-delete(collection("holdings")/holdings[userid = "Minollo"]) 
Other examples can be found in the RDBMSUpdate example.