Creating an Update Function Call

In Stylus Studio, you can create an update function call using

l Drag-and-drop

l The short-cut menu on the Mapper canvas

When you use drag-and-drop, Stylus Studio creates the link from the update function block for you, which automatically commits the XQuery mapping to Stylus Studio.

How to Create an Update Function Call

To create an update function call:

1. Connect to the relational database that contains the table or tables you want to update. See Creating a Database Connection if you need help with this step.

2. Drag the table you want to update from the File Explorer and drop it on the XQuery Mapper canvas.

3. Map the repeating element that represents the table you want to update to the first input port ($row-node as element()*).

4. Create a FLWOR block to locate the record you want to update in the database table. See Creating a FLWOR Block if you need help with this step.

5. Map the output of the FLWOR block to the flow port on the top of the update function block.

6. Create a condition expression to select the records you want to update. See Creating a Function Block if you need help with this step.

7. Expand the update function block by double-clicking it.

8. Double-click the input port that corresponds to the column whose value you want to change.

9. Save and preview the XQuery; check results on the database table you have updated.

Alternative:

1. Right-click the Mapper canvas.

2. Select Function Block > DataDirect XQuery > sql-update from the short-cut menu.

The update function block appears on the Mapper canvas.

3. When you have finished defining the XQuery, map the update function block's output port to the Set Target Document pane. Your XQuery mapping is not committed to Stylus Studio until you complete this step.

Example

In this example, we update the Shippers table to change the telephone for the Speedy Express company.

If we double-click the Shippers table in the File Explorer, the Shippers table is rendered as XML. Looking at Grid tab, we can see that the Shippers table has three records, and that the current telephone number for Speedy Express is (503) 555-9831.

Figure 383. Shippers Table

To get started, we create a new XQuery and save it as UPDATE.xquery. As with the insert function, we drag and drop the table from the File Explorer and drop it on the XQuery Mapper canvas. After dropping the table, choose Create SQL Update Call from the short-cut menu to add the SQL block and the corresponding code to the XQuery.

In order to update a record, we need to:

l Specify the table whose records we need to fetch

l Fetch the record we want to update

l Specify a new value for the table column we want to change

First, we map the Shippers repeating element to the first port ($row-node as element()*) on the SQL block created for the update function. This creates a collection based on the Shippers table. (See Using the collection() Function in Stylus Studio for more information on collections.)

Next, we create a FLWOR expression to iterate over the Shippers table by mapping the Shippers repeating element to the For port on the FLWOR block, and mapping the FLWOR block's output port to the flow port on the SQL block. At this point, our XQuery Mapper canvas, and the resulting XQuery code, looks something like this:

Figure 384. FLWOR Expression Iterates Over the Shippers Table

Next we need to specify which record we want to update, and how we want to change it. We'll do this by creating a simple condition expression that locates the shipper whose phone number we want to change (the ShipperID for Speedy Express is 1). To do this, we

l Add the Equal conditional block to the Mapper canvas

l Map the ShipperID node to the block's first input port

l Double-click the second input port to specify the ShipperID value for the record we want to update

As you can see from the resulting XQuery code, the update function still needs values specified for the column in the Shippers table we want to update, and the new value