Talk to the Experts

In  the last post, I discussed using functions within XQuery with the Substring() function as part of the XQuery for the Non-Expert blog series.  To continue on that theme, this post is going to take a look into the available aggregates within XQuery and demonstrate how to use them.

Aggregate Choices

There are five aggregate functions available within XQuery.  These should all be fairly familiar to you.  They are some of the most common aggregates around.

These functions are:

• count(): Counts the number of values in a list of values.
• min(): Returns the maximum value in a list of values.
• max(): Returns the maximum value in a list of values.
• avg(): Returns the average of a list of numeric values.
• sum(): Returns the sum of a list of numeric values.

The syntax for each of these functions is as follows:

fn:count($arg as item()) as xs:integer
fn:avg($arg as xdt:anyAtomicType) as xdt:anyAtomicType
fn:min($arg as xdt:anyAtomicType) as xdt:anyAtomicType
fn:max($arg as xdt:anyAtomicType) as xdt:anyAtomicType
fn:sum($arg as xdt:anyAtomicType) as xdt:anyAtomicType

The $arg as an item() can be either an element or an attribute.  As an xdt:anyAtomicType the $arg value can be an attribute.  These elements and attributes are constructed as paths through the XML document.

Aggregate Demonstrations

Before we start the demonstrations, we’ll need a sample XML document to use with these functions.  As with the previous examples, let’s use a document similar to the one that was utilized in the previous posts:

IF OBJECT_ID('tempdb..#ValueExample') IS NOT NULL
    DROP TABLE #ValueExample
GO
CREATE TABLE #ValueExample ( XMLDocument XML )
GO
INSERT  INTO #ValueExample
VALUES  ( '<Building type="skyscraper">Sears Tower
<Floor level="1" Color="Lt-Blue"/>
<Floor level="2" Color="Dk-Red">
<Room number="2.1" />
</Floor>
<Floor level="3" Color="Md-Yellow">
<Room number="3.1" beds="1"/>
<Room number="3.2" beds="2"/>
</Floor>
<Floor level="4" Color="Lt-Purple">
<Room number="4.1" beds="1"/>
<Room number="4.2" beds="4"/>
</Floor>
</Building>' )
GO
SELECT  XMLDocument
FROM    #ValueExample
GO

Count() Function

Let’s start with the easiest of the functions.  Suppose we wanted to count all of the Room elements for each Floor element.  Or, if we wanted to all of the bed attributes within those Room elements.

The query for this would be:

SELECT c.query('.') AS XMLFragment
,c.value('count(Room)','int') AS CountRooms
,c.value('count(Room/@beds)','int') AS CountRoomsWithBeds
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)

With the following results:

The query is able to discern when either the element or attributes are available in the XML fragment.   As is shown above, Floor 2 has one room while it does not have any beds.  Where Floor 1 has neither rooms nor beds.

Min() Function

From here, we’ll look at the min() function.  In this case, let’s find the minimum number of beds that exist in any of the rooms.  This value comes from the @bed attribute under the Room element.

The query for this would be:

SELECT c.query('.') AS XMLFragment
,c.value('min(Room/@beds)','int') AS MinBeds
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)

With the following results:

Max() Function

Similar to the min() function, the max function return values from element or attribute.  In this case though, it will return the maximum value for the element or attribute within the XML node.  From the perspective of the demonstration script, this would be similar to seeking the maximum number of beds per Room on each Floor.

The query for this would be:

SELECT c.query('.') AS XMLFragment
,c.value('max(Room/@beds)','int') AS MaxBeds
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)

With the following results:

Avg() Function

Now on to the avg() function; this function will provide the average of all of the values from elements or attributes across an XML node.  In this example, we’ll determine the average number of beds per Rooms per Floor.

The query for this would be:

SELECT c.query('.') AS XMLFragment
,c.value('avg(Room/@beds)','decimal(3,1)') AS AverageBeds
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)

With the following results:

Sum() Function

Last, but not least, is the sum() function.  As the definition at the beginning of the posts states, this function adds all of the values from elements or attributes across an XML node.  To demonstrate, let’s count all of the beds for each Floor in the XML document.

The query for this would be:

SELECT c.query('.') AS XMLFragment
,c.value('sum(Room/@beds)','float') AS TotalBeds
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)

With the following results:

Aggregate Wrap-Up

These examples are fairly simple, but they should provide an idea for how to leverage aggregates within XQuery statements.  In the examples, I used the value() function to return the values for each of the result sets.  This isn’t the only method for accomplishing this, the query() function can also be used.  As with the other posts, if anything in this post needs further clarity please comment below.

Related posts:

1. XQuery for the Non-Expert – Substring() Function
2. XQuery for the Non-Expert – Value
3. XQuery for the Non-Expert – Query