Manipulating repeating fields in a message tree

Suppose that you want to perform a special action on invoices that have a total order value greater than a certain amount. To calculate the total order value of an Invoice field, you must multiply the Price fields by the Quantity fields in all the Items in the message, and total the result. You can do this using a SELECT expression as follows:

(
 SELECT SUM( CAST(I.Price AS DECIMAL) * CAST(I.Quantity AS INTEGER) )
  FROM Body.Invoice.Purchases."Item"[] AS I
) 

The example assumes that you need to use CAST expressions to cast the string values of the fields Price and Quantity into the correct data types. The cast of the Price field into a decimal produces a decimal value with the natural scale and precision, that is, whatever scale and precision is necessary to represent the number. These CASTs would not be necessary if the data were already in an appropriate data type.

The SELECT expression works in a similar way to the quantified predicate, and in much the same way that a SELECT works in standard database SQL. The FROM clause specifies what is being iterated, in this case, all Item fields in Invoice, and establishes that the current instance of Item can be referred to using I. This form of SELECT involves a column function, in this case the SUM function, so the SELECT is evaluated by adding together the results of evaluating the expression inside the SUM function for each Item field in the Invoice. As with standard SQL, NULL values are ignored by column functions, with the exception of the COUNT column function explained later in this section, and a NULL value is returned by the column function only if there are no non-NULL values to combine.

The other column functions that are provided are MAX, MIN, and COUNT. The COUNT function has two forms that work in different ways with regard to NULLs. In the first form you use it much like the SUM function above, for example:

SELECT COUNT(I.Quantity)
  FROM Body.Invoice.Purchases."Item"[] AS I

This expression returns the number of Item fields for which the Quantity field is non-NULL. That is, the COUNT function counts non-NULL values, in the same way that the SUM function adds non-NULL values. The alternative way of using the COUNT function is as follows:

SELECT COUNT(*)
  FROM Body.Invoice.Purchases."Item"[] AS I

Using COUNT(*) counts the total number of Item fields, regardless of whether any of the fields is NULL. The preceding example is in fact equivalent to using the CARDINALITY function, as in the following example:

CARDINALITY(Body.Invoice.Purchases."Item"[])

In all the examples of SELECT given here, just as in standard SQL, you could use a WHERE clause to provide filtering on the fields.