Retrieve an average number of chars in XML column in DB2

In case if you have an XML column content in your youtTable and you want to count an average number of characters (or min, max) in the column you can use this code. 

1. set schema yourSchema;
2. SELECT avg(xtab.length) 
3.    FROM URCH_POSTS, 
4.        XMLTABLE(     
5.           'for $i in $c 
6.         let $len := fn:string-length($i)
7.       return {$len}'
8.       passing youtTable.content as "c"
9.       COLUMNS 
10.               length INTEGER PATH '.')  
11.                                                            as xtab;

In 4th line we are starting to create a XMLTABLE

In 5th line we start an XQuery (') for loop over every line of $c where $c is defined in the line 8 (just a value from you column)

In 6th line we define our return value. It should be in the XML form. And finish our XQuery (')

In 9th line we start to define columns that will be returned in the XMLTABLE

In 10th line we name our column length, define its type INTEGER and the path in the resulted from the line 7 XML string. 

In the 11th line we name the output

In the 2nd line we retrieve the output as an INTEGER column and therefore we can run corresponding operations. 

One can  refine the code by calculating the average in the XQuery directly using fn:avg