6

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'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="root"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="val" minOccurs="0" maxOccurs="unbounded" type="xsd:string" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>';
GO
DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';

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) = '<root><val>1</val><val>2</val><val>3</val></root>';
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) = '<root><val>1</val><val>2</val><val>3</val></root>';
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) = '<root><val>1</val><val>2</val><val>3</val></root>';
SELECT @xml.value('sum(xs:integer((//val)))','INT')
GO
DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';
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 should be able to do this:

DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';
SELECT @xml.value('sum((//val)! xs:integer(.))','INT')

However, that is seems to not be implemented by SQL Server:

Msg 2217, Level 16, State 1, Line 21
XQuery [value()]: ',' or ')' expected

Option 5:

You could use the .nodes() function and solve the problem outside of xquery:

DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';
SELECT SUM(X.V.value('.','INT')) FROM @xml.nodes('//val') X(V)

This results in the correct value of 6. However, I am looking for a solution within xquery, so this is not an option for me.


Option ?:

With that I am out of ideas. Is there a solution to this problem?



Extra:

In addition, I would really like to know what the ? stands for in this line, taken from one of the examples above:

SELECT @xml.value('sum(((//val)[1] cast as xs:integer ?))','INT')

SQL Server requires it. You get this error, if you try without:

Msg 9301, Level 16, State 1, Line 18
XQuery [value()]: In this version of the server, 'cast as <type>' is not available. Please use the 'cast as <type> ?' syntax.

This error hints at the fact, that this requirement is a limitation of SQL Server. However, I was not able to find documentation about its meaning anywhere.

4

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.

enter image description here

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.