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!
No comments:
Post a Comment