XQuery for the SQL programmer – Updating your database

XQuery for the SQL programmer – Updating your database

September 11, 2008 0 Comments

In today’s post we’ll show how to update your relational database using XQuery. As for all posts in this series, we’ll start with SQL statements, and show the equivalent xqueries.

The XQuery Update Facility is an upcoming W3C standard, currently in Candidate Recommendation status. The XQuery Update Facility extends XQuery 1.0 and allows to change XML. However, today we will not get into the details of this upcoming standard. We will however show how XQuery allows you to update your RDBMS, in a similar way as SQL does, using the DataDirect XQuery update functionality.

Basically, DataDirect XQuery introduces 3 new XQuery functions,

You probably already see the analogy with SQL’s INSERT, UPDATE and DELETE statements, time for some examples.

Inserting rows

The following SQL statement creates a new user, Joe Roxy,

[cc lang="sql"]insert into USERS(USERID, NAME) values ('U10', 'Joe Roxy')[/cc]

The XQuery variant is very similar,

[cc lang="xquery"]ddtek:sql-insert("USERS", "USERID", "U10", "NAME", "Joe Roxy")[/cc]

The first argument identifies the table which we want to insert into. Next ddtek:sql-insert has a variable list of column-value pairs.

A "insert into select" is a bit more verbose in XQuery than it is in SQL.

[cc lang="sql"]insert into NEWUSERS select * from USERS where NAME LIKE 'Tom%'[/cc]

In XQuery you would write,

[cc lang="xquery"]for $user in collection("USERS")/USERS where starts-with($user/NAME, "Tom") return ddtek:sql-insert("OTHERUSERS", "USERID", $user/USERID, "NAME", $user/NAME, "RATING", $user/RATING) [/cc]

Deleting rows

Let’s now delete Joe Roxy from the user’s table,

[cc lang="sql"]delete from USERS where USERID = 'U10'[/cc]

With DataDirect XQuery you use ddtek:sql-delete. You select all the users you want to delete, we know how we can do that,

[cc lang="xquery"]collection("USERS")/USERS[USERID = 'U10'][/cc]

And now you pass that information as argument to ddtek:sql-delete,

[cc lang="xquery"]ddtek:sql-delete(collection("USERS")/USERS[USERID = 'U10'])[/cc]

And of course, if you have a preference for FLWOR expressions, the following is equivalent,

[cc lang="xquery"]for $u in collection("TESTUSERS")/TESTUSERS where $u/USERID = 'U10' return ddtek:sql-delete($u) [/cc]

Updating rows

Let’s now show how to update the item 1001, change its description to Green Bicycle and price = 100,

[cc lang="sql"]update ITEMS set DESCRIPTION = 'Green Bicycle', RESERVE_PRICE = '100' where ITEMNO = '1001'[/cc]

Similar to deleting rows, you first of all need to specify which rows to be updated, and pass that as argument to ddtek:sql-update.

[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where $item/ITEMNO = '1001' return ddtek:sql-update($item, "DESCRIPTION", "Green Bicycle", "RESERVE_PRICE", 100) [/cc]

Just as in SQL, you can update a column with a value computed from the current value. Suppose we want to raise the reserve price for all items with 10%. As shown in the following SQL statement,

[cc lang="sql"]UPDATE "xvs"."xvs001"."ITEMS" SET "RESERVE_PRICE" = "RESERVE_PRICE" * 1.10[/cc]

And in XQuery,

[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS return ddtek:sql-update($item, "RESERVE_PRICE", $item/RESERVE_PRICE * 1.10) [/cc]


Several SQL implementations support upsert or mergefunctionality. It allows to update a target table with data from a source table. Rows in the target that match the source are updated, and the non existing rows are inserted. XQuery has not such built-in functionality, but there is also no need to. Using a conditional expression, this is easily expressed in XQuery.

Suppose we want to copy all users with rating A from one to another table. If the user exists, we update the row, otherwise we insert. We iterate over all rows in the USERS table with rating A, and lookup the matching row in the NEWUSERS table. If a match in NEWUSERS is found, we update the row, otherwise a new row is inserted,

[cc lang="xquery"]for $source in collection("USERS")/USERS let $target := collection("NEWUSERS")/NEWUSERS[USERID = $source/USERID] where $source/RATING = "A" return if (exists($target)) then ddtek:sql-update($target, "NAME", $source/NAME, "RATING", $source/RATING) else ddtek:sql-insert("NEWUSERS", "USERID", $source/USERID, "NAME", $source/NAME, "RATING", $source/RATING) [/cc]

NULL values

As we learned in The Data Model, in the SQL/XML view, NULL values area represented as missing elements. Similar, to pass NULL to one of the ddtek:sql-* functions, specify the empty sequence.

Setting an item’s description to null in SQL,

[cc lang="sql"]update ITEMS set DESCRIPTION = NULL where ITEMNO = '1001'[/cc]

Updating a columns with NULL, is not much different in XQuery,

[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS return ddtek:sql-update($item, "DESCRIPTION", ()) [/cc]

All the update examples in this post either use static data (literals) or are about copying data from one to another table. But in most scenarios this data comes out of your application in one or the other way.

With DataDirect XQuery you have the ability to read data out of an XML structure and update your database. Minollo and I have blogged on this subject before in Bulk load of XML data into a relational database and Shredding XML documents into tables, a database independent approach....

As you know in SQL, bind markers facilitates binding values from your application into the SQL statement. What is the equivalent of SQL bind markers in XQuery? Watch out for our next post in the XQuery for the SQL programmer series.

Tech Tags:

Marc Van Cappellen

View all posts from Marc Van Cappellen on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.

Read next Improve Log Management with OpenEdge SQL Logging
Comments are disabled in preview mode.