Handling multiple rows from SQL

How your Databoxes can handle multiple rows of data

In most cases, SQL Query Databoxes have something in common - they must return a single row of data.

However, see below for an example of what can be achieved when handling multiple rows with some clever use of XML and XPath statements.(you may want to seek local IT support regarding SQL, XML and XPath at this level; the Omfax Systems Administrator training is not intended to cover these technologies in any depth).

Example

A recent requirement arose where is was necessary to scan and interpret multiple rows. In this case, it was concerning asbestos details for a particular location (in this example,KITchen) for a given property. This is the underlying data we'll be working with; it comprises two rows which detail the asbestos component and associated risk level:

(Results obtained from SSMS)

So we created an SQL Query Databox which returns the above data in an XML packet (using the FOR XML clause):

SELECT 
	ASBESTOS_COMP + ':' + RISK_LEVEL AS ASBText, 
	RISK_LEVEL 
FROM 
	usr_Asbestos AS ASB 
WHERE 
	SUB_LOC_CODE = 'KIT' 
FOR 
	XML AUTO, TYPE, ROOT('Custom')

and this is the XML packet created:

<Custom>
  <ASB ASBText="FLRTILE   :LOW       " RISK_LEVEL="LOW       " />
  <ASB ASBText="INSULPAD  :HIGH      " RISK_LEVEL="HIGH      " />
</Custom>

This allows us to use XPath expressions to access the returned row(s) in a variety of ways:

Expressions using xPath constructs

Here's an explanation of the above Expressions:

Title	Value	Description
Count non-zero risk	Item("count(ASB[@RISK_LEVEL != 'ZERO'])")	As the title suggests, this will return the total number of ASB nodes (i.e. rows) where the attribute RISK_LEVEL is not equal (i.e. '!=') to the literal 'ZERO'. IMPORTANT: Note that 'count' is an XPath function so remember that XPath is case-sensitive; if you specified 'Count', an error will be returned.
Count VLOW2	Item("count(ASB[@RISK_LEVEL = 'VLOW2'])")	This expression will return the number of ASB nodes (i.e. rows) where the RISK_LEVEL equals 'VLOW2'.
Text	Item("ASB/@ASBText")	This will return the a comma separated list of the values held in each ASBText attribute.

Using Expressions with your DataBoxes in this way will allow you to add value to messages and generally control scriptflow based on the results returned, i.e. in this case:

The Expression's results

Used with care, the combination of SQL, XML and XPath offer some extremely powerful and interesting possibilities!