Earlier this week I had asked “What pureXMLness do you have?”. Let me explain the query that computes the pureXMLness today.

xquery

let $i:=db2-fn:sqlquery("select xmlcast(count(*) as xml) from sysibm.sysindexxmlpatterns"),

$j:=db2-fn:sqlquery("select xmlcast(count(*) as xml) from sysibm.syscolumns where coltype='XML'"),

$k:=db2-fn:sqlquery("select xmlcast(count(*) as xml) from sysibm.sysxmlstrings"),

$l:=db2-fn:sqlquery("select xmlcast(count(*) as xml) from sysibm.sysxmlpaths"),

$m:=db2-fn:sqlquery("select xmlcast(count(*) as xml) from sysibm.sysxsrobjects"),

$n:=db2-fn:sqlquery("select xmlcast(cast (avg(xda_hit_ratio_percent) as float) as xml) from sysibmadm.bp_hitratio")

return <pureXMLness>{avg((($i+$j+$m) div 20,($k+$l) div 300,$n))}</pureXMLness>;

First, let me start with the first keyword, “``` xquery

What then follows is a so-called [let statement](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/xqrfrltcls.html) ("```
let $i [...]
```"). It is used to define the variables $i to $n. For each of the variables a value is assigned. All of them are computed using the [sqlquery](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/xqrfnsqq.html) function which executes a SQL SELECT statement. One requirement is that the function sqlquery returns an XML sequence. The SQL statements that are executed are simple SELECT statements using the count() or avg() function. To convert the results of count() and avg() to an XML sequence, we use the [XMLCAST](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0023486.html) function (```
xmlcast(count(*) as xml)
```). The SQL value is converted into an XML value (which is a XML sequence).



As input to the pureXMLness we take the number of entries in the [indexxmlpatterns](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0021699.html) catalog table into account, i.e., the number of indexes over XML columns. Another variable is the number of XML-typed columns obtained from the [columns](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0001038.html) catalog table. Three other variables are based on the number of stringIDs (sysxmlstrings), pathIDs (sysxmlpaths), and the registered XML schemas ([xsrobjects](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0021697.html)). The last input variable is the average [bufferpool hit ratio](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0022013.html) for XDA objects.



After all the variables have been computed, they can be used in the [return clause](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/xqrretrcls.html) to compose the pureXMLness which is packed into an XML element.