XQuery/Queries on Tables

= Motivation = We are frequently given data in a tabular structure and we are required to extract data relative to a position within the table. For example we might need to fill the data in a cell of a table that has the sum of the other values in that column.

= Method = We will build a library of XQuery functions that uses XPath expressions to get values from the table assuming you are at some position in the table.

= Sample Input = Here is a sample of a table where each cell has the row and column number within the table data element:

= Sample functions = Here are three functions that extract a cell, a row and a column

This function takes the input table and removes all but a single row and single column using predicates within the XPath expression. So to get the second row and third column the executed expression is: $table/tr[2]/td[3]

This function simply gets the  element that contains the current cell we are in. So if we run

$row := local:current-row( local:cell($table, 2, 3) )

we would get back

Our last utility function will find all the cells of a column of a table. Here is the code to do this: This function is a bit more complex. We need to first figure out what column we are within the table. To do this we will use the preceding-sibling XPath axis expression to count the number of prior cells within the table. We then add one so that if there are no prior columns we will be on the first column. We could have also used preceding-sibling::* if we were not sure that some cells used other element names such as  for table headers. Once we know what column we are in we can just return the table which we get by adding ../.. and then get all rows and only our current column with /tr/td[$col-num].

= Sample Driver Query =

which returns

= Adding Calculations to a Table = Now that we have a strategy for getting the rows and columns of a cell, lets add two calculation types to our table. We will modify our table so that it contains only digits or the expressions {rowsum} or {colsum}. The table will look like the following:

Table with data and operations
Now we will need a function that replaces each calculation with the values. We will also replace the functions for each row and column with functions that sum the values that are castable as digits.

full source code
};

let $title := 'table queries'

let $table :=

(: get the nth row and nth column :) let $cell-r2-c3 := local:cell($table, 2, 3)

return {local:transform-table($table)}

Table with row and column totals
Which returns the following results (need screen image here):

Note that the final colsum of rowsums, the result of calculations is zero. This is due to the fact that the totals of row-specific sub-totals are not yet calculated and placed in the original table before the colsum is done. To fix this we could do one or more of the following:
 * Use updates (XQuery updates) to update the table after each operation
 * a new tablesum function
 * generalized the functions to work more like a spreadsheet.

To do this the table would need to be updated with the sums as they are calculated. But the order of the totals may not be correct unless we use a dependency graph to understand the order that calculations should occur. This can easily be done by using an XForms framework.