How to cast a list of values within xquery in SQL Server?

I have the following T-SQL code that I am trying to get to work:

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'TST') DROP XML SCHEMA COLLECTION TST; 
GO
CREATE XML SCHEMA COLLECTION TST AS N'';
GO
DECLARE @xml XML(TST) = '123';

SELECT @xml.value('sum(//val)','INT')

The xsd tells us that the val node is of type string. Therefor the sum() function won't work:

Msg 9308, Level 16, State 1, Line 7
XQuery [value()]: The argument of 'sum()' must be of a single numeric primitive type or 'http://www.w3.org/2004/07/xpath-datatypes#untypedAtomic'. Found argument of type 'xs:string *'.

I have tried the following ways to get this to work:

Option 1:

One work-around is to just get rid of the xsd schema:

DECLARE @xml XML(TST) = '123';
SELECT (CAST(@xml AS XML)).value('sum(//val)','INT')

That returns the correct result: 6. However, it seems rather crude and might not always be an option (e.g. when dealing with XML indexes).

(Non-)Option 2:

An alternative is, to cast each value to xs:integer before summing them together. However, that is where I am stuck.

It is easy to cast a single element:

DECLARE @xml XML(TST) = '123';
SELECT @xml.value('sum(xs:integer((//val)[1]))','INT')

But that results in 1, which is the wrong result. This is expected, as now (because of the [1]) only the first val node gets looked at.

(Non-)Option 3:

There are two ways to write a cast in xquery:

DECLARE @xml XML(TST) = '123';
SELECT @xml.value('sum(xs:integer((//val)))','INT')
GO
DECLARE @xml XML(TST) = '123';
SELECT @xml.value('sum(((//val) cast as xs:integer ?))','INT')

However, both work only with singletons as input:

Msg 2365, Level 16, State 1, Line 15
XQuery [value()]: Cannot explicitly convert from 'xs:string *' to 'xs:integer'
Msg 2365, Level 16, State 1, Line 18
XQuery [value()]: Cannot explicitly convert from 'xs:string *' to 'xs:integer ?'

(Non-)Option 4:

According to the xquery documentation you

You can do it in xQuery with a FLWOR Statement and Iteration (XQuery) using cast on each value returned.

SELECT @xml.value('sum(for $val in //val return $val cast as xs:int?)','INT')


A query like that could make you think that SQL Server will do some time consuming looping operations but if you have a look at the query plan you will see that is not the case. The for loop is transformed to a plan that gets all the values from a single call to a Table-valued function, converts them to an integer and then using an aggregate operator to calculate the sum.

StackExchange Database Administrators Q#126327, answer score: 4