Creating an Insert Function Call

In Stylus Studio, you can create an insert 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 insert function block for you, which automatically commits the XQuery mapping to Stylus Studio.

How to Create an Insert Function Call

Use the following procedure to create an insert function call in XQuery Mapper. Note that specifics will vary based on the requirements for your XQuery - you might need to use an equal condition instead of a greater-than condition to select records, for example.

To create an insert 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. In the File Explorer, expand the database tree to expose the table you want to update.

3. Drag the table from the File Explorer and drop it on the Mapper canvas.

A short-cut menu appears with two choices: Create SQL Insert Call and Create SQL Update Call.

4. Select Create SQL Insert Call.

The function block for the ddtek:sql-insert call appears on the Mapper canvas.

5. Create a FLWOR block to loop over the database table. See Creating a FLWOR Block if you need help with this step.

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

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

8. Map source document nodes to the corresponding input ports on the function block.

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-insert from the short-cut menu.

The insert function block appears on the Mapper canvas.

3. When you have finished defining the XQuery, map the insert 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 use the SQL insert function call to add a new set of records to the Products table.

In a new XQuery that we have saved as INSERT.xquery, we drag the Products table and drop it on the XQuery Mapper canvas. After choosing Create SQL Insert Call from the short-cut menu, the database table is automatically added as a source for the XQuery, and that the outline for the DataDirect XQuery ddtek:sql-insert function is displayed in the Mapper's text pane:

Figure 378. Using a Relational Table to Create a SQL INSERT

Next, we create a FLWOR block (right-click on the Mapper canvas and select FLWOR Block) to loop through all the records in the Products table. We use the Products repeating element to specify the FLWOR block's Where clause, and map its output to the flow port on the SQL block. Again, notice that the XQuery code displayed in the text pane is updated with the code for the FLWOR block:

Figure 379. FLWOR Block to Loop Through Database Table Records

Values for new records can come from existing records if they are mapped; otherwise, they are created as empty records or use default values depending on how the database is configured.

For this example, let's use existing values for the ProductName, SupplierID, and CategoryID columns. In order to map these values, we first expand the SQL block created for the Products table by double-clicking it, and then mapping the element names in the Add Source Document pane to the corresponding ports on the SQL block: