Home:Learn XQuery:XQuery Examples:XQuery Updates:Insert, Update, Delete

ddtek:sql - Insert, Update, Delete

Learn to: insert a single record in a database table, update records in a database table, and delete records in a database table.

ddtek:sql-insert

This 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();

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) 

ddtek:sql-update

This 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, 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) 

ddtek:sql-delete

This 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"])