SQL Server xquery sum cast error when schema data type is string

Question

Trying to run this in SQL Server 2014 in order to sum all Values in "UserData" xml:

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'SC')
    DROP XML SCHEMA COLLECTION SC 
go
CREATE XML SCHEMA COLLECTION SC AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="UserData"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Item" minOccurs="0" maxOccurs="unbounded"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Value" type="xsd:string" /><xsd:any minOccurs="0" /></xsd:sequence><xsd:attribute name="Key" type="xsd:string" /><xsd:attribute name="Type" type="xsd:string" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'
go
Declare @xml xml(SC)
set @xml=   '<UserData>
                <Item Key="CONVERTED_PAGES_1" Type="CONVERTED_PAGES">
                    <Value>2</Value>
                </Item>
                <Item Key="CONVERTED_PAGES_2" Type="CONVERTED_PAGES">
                    <Value>4</Value>
                </Item>
            </UserData>'

Select @xml.value('sum(/UserData/Item[@Type="CONVERTED_PAGES"]/Value)','int') as Sum

and getting the following error:

Msg 9308, Level 16, State 1, Line 16 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 tried changing the select to the following:

Select @xml.value('sum(/UserData/Item[@Type="CONVERTED_PAGES"]/Value cast as xs:int?)','int') as Sum

But then I get this:

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

I am not able to change the xml schema in this case, but figured I could cast in order to perform this operation (since I know that in my case all of the Values will be int). Any suggestions would be appreciated!

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

The xquery sum aggregate requires the input to be a number. Currently it is defined as string in your XSD. To get this to work, you have three options:

Option 1:

You change the schema to force "value" to be an int. Instead of the first line below, use the second. (The difference is highlighted in between the two statements with "|||||||".)

Query 1:

CREATE XML SCHEMA COLLECTION SC AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="UserData"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Item" minOccurs="0" maxOccurs="unbounded"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Value" type="xsd:string" /><xsd:any minOccurs="0" /></xsd:sequence><xsd:attribute name="Key" type="xsd:string" /><xsd:attribute name="Type" type="xsd:string" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'
                                                                                                                                                                                                                                                                                                                                                                                                          |||||||              
CREATE XML SCHEMA COLLECTION SC AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="UserData"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Item" minOccurs="0" maxOccurs="unbounded"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Value" type="xsd:integer" /><xsd:any minOccurs="0" /></xsd:sequence><xsd:attribute name="Key" type="xsd:string" /><xsd:attribute name="Type" type="xsd:string" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'

Option 2:

If changing the XSD is not an option, you can also use the T-SQL SUM aggregate instead of the xquery one, like this:

Query 2:

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'SC')
    DROP XML SCHEMA COLLECTION SC 
go
CREATE XML SCHEMA COLLECTION SC AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="UserData"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Item" minOccurs="0" maxOccurs="unbounded"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Value" type="xsd:string" /><xsd:any minOccurs="0" /></xsd:sequence><xsd:attribute name="Key" type="xsd:string" /><xsd:attribute name="Type" type="xsd:string" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'
go
Declare @xml xml(SC)
set @xml=   '<UserData>
                <Item Key="CONVERTED_PAGES_1" Type="CONVERTED_PAGES">
                    <Value>2</Value>
                </Item>
                <Item Key="CONVERTED_PAGES_2" Type="CONVERTED_PAGES">
                    <Value>4</Value>
                </Item>
            </UserData>'

SELECT SUM(N.value('.','INT')) AS [Sum]
  FROM @xml.nodes('/UserData/Item[@Type="CONVERTED_PAGES"]/Value') AS X(N);

Option 3:

As you noticed, SQL Server does not allow us to convert an XSD-typed value to another data type. To get around that, you could instruct SQL Server to forget about the schema:

Query 3:

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'SC')
    DROP XML SCHEMA COLLECTION SC; 
GO
CREATE XML SCHEMA COLLECTION SC AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="UserData"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Item" minOccurs="0" maxOccurs="unbounded"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Value" type="xsd:string" /><xsd:any minOccurs="0" /></xsd:sequence><xsd:attribute name="Key" type="xsd:string" /><xsd:attribute name="Type" type="xsd:string" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>';
GO
DECLARE @xml XML(SC);
SET @xml=   '<UserData>
                <Item Key="CONVERTED_PAGES_1" Type="CONVERTED_PAGES">
                    <Value>2</Value>
                </Item>
                <Item Key="CONVERTED_PAGES_2" Type="CONVERTED_PAGES">
                    <Value>4</Value>
                </Item>
            </UserData>';

SELECT CAST(@xml AS XML).value('sum((/UserData/Item[@Type="CONVERTED_PAGES"]/Value ))','int') AS Sum;

Note: Without the schema, you still cannot cast (not sure why), but the sum now works without casting.

Update:

I did a little more digging. The original error message you got after attempting to cast is this one:

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

It tells us that you can't convert a sequence of strings into a single integer.

The * as well as the ? are Occurrence Indicators. So the error message reads: zero-to-many strings can't be converted to zero-to-one integer.

Your xquery /UserData/Item[@Type="CONVERTED_PAGES"]/Value returns more than one value, and to sum them up we need to convert each one individually.

xquery offers multiple ways to accomplish that, but not all of them work in SQL Server. The one that works uses a for-each construct:

.value('sum(for $val in /UserData/Item[@Type="CONVERTED_PAGES"]/Value return $val cast as xs:int?)','INT');

Thanks to @MikaelEriksson for helping me out with this.

Thanks Sebastian! Those last two are perfect! I ended up using the last one because I am really querying a table that has a "UserData" column, and therefore want to get the sum in every result row (which your "Option 3" seems to lend itself to best). — Jon, Jan 14 '16 at 21:10

SQL Server xquery sum cast error when schema data type is string

1 Answers1