> For the complete documentation index, see [llms.txt](https://docs.keyfax.biz/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.keyfax.biz/product-suite/admin/best-practices/handling-multiple-rows-from-sql.md).

# Handling multiple rows from SQL

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](https://www.w3schools.com/xml/) and X[Path](https://www.w3schools.com/xml/xpath_intro.asp) 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,**KIT**chen) 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:  

<div align="center"><figure><img src="/files/gEUEQ5tUtvI4XhdUZa5M" alt=""><figcaption><p>(Results obtained from SSMS)</p></figcaption></figure></div>

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

```sql
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:

```xml
<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:

<div align="center"><figure><img src="/files/8esbF7okYM3a1Zt7ReMx" alt=""><figcaption><p>Expressions using xPath constructs</p></figcaption></figure></div>

Here's an explanation of the above Expressions:

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

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:

<div align="left"><figure><img src="/files/ev2Ng8qIxoNyp70vG8EE" alt=""><figcaption><p>The Expression's results</p></figcaption></figure></div>

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