SQL Server的奇怪行为时，和节点在XML值(strange behavior of SQL

我想问一个有关和节点的价值观问题：

在SQL Server 2008中总结一些XML节点的值

请考虑以下代码：

Declare @xml xml 
set @xml='<Parent ID="p">
     <Child ID="1">1000000000</Child > 
     <Child ID="2">234650</Child > 
     <Child ID="3">0</Child > 
      </Parent >'

Select @xml.value('sum(/Parent[@ID="p"]/Child)','bigint') as Sum

如果您执行这一点，retrun此错误：

消息8114，级别16，状态5，第8行误差变换数据类型为nvarchar为bigint。

问题是它返回该值： 1.00023465E9

如果我改变上述查询这种方式即可：

Declare @xml xml 
set @xml='<Parent ID="p">
     <Child ID="1">1000000000</Child > 
     <Child ID="2">234650</Child > 
     <Child ID="3">0</Child > 
      </Parent >'

Select @xml.value('sum(/Parent[@ID="p"]/Child)','float') as Sum

为什么Sql Server中做到这一点？

Answer 1:

SQL Server有一个问题从一个字符串转换用科学记数法的数值为整数，当您运行XPath查询作为会发生，但是，它可以做到这一点的float 。

你可以写你的查询是这样的：

select @xml.value('sum(/Parent[@ID = "p"]/Child) cast as xs:long?', 'bigint')

Answer 2:

试试这个 -

DECLARE @xml XML 
SELECT @xml='<Parent ID="p">
     <Child ID="1">1000000000</Child > 
     <Child ID="2">234650</Child > 
     <Child ID="3">0</Child > 
      </Parent >'

SELECT @xml.value('sum(for $r in /Parent[@ID="p"]/Child return xs:int($r))', 'bigint')

更新：

DECLARE @xml XML 
SELECT @xml='<Parent ID="p">
     <Child ID="1">100000000000000</Child > 
     <Child ID="2">234650</Child > 
     <Child ID="3">0</Child > 
      </Parent >'

SELECT @xml.value('sum(for $r in /Parent[@ID="p"]/Child return xs:decimal($r))', 'bigint')

更新2：

DECLARE @xml XML 
SELECT @xml='<Parent ID="p">
     <Child ID="1">100000000000000.6</Child > 
     <Child ID="2">234650</Child > 
     <Child ID="3">0</Child > 
      </Parent >'

SELECT @xml.value('sum(for $r in /Parent[@ID="p"]/Child return xs:decimal($r))', 'decimal(18,2)')

Answer 3:

试试这个正在与BIGINT：

DECLARE @SearchKeyWords XML 

SET @SearchKeyWords =
'<Parent ID=''p''>
    <Child ID="1">1000000000</Child > 
    <Child ID="2">234650</Child > 
    <Child ID="3">0</Child > 
</Parent >' 

DECLARE @TempSearchKeyWords TABLE
        (
            SearchKeyWord BIGINT
        )

INSERT INTO @TempSearchKeyWords                     
SELECT SearchKeyWords.SearchKeyword.value('.',' bigint ') AS SearchKeyword             
FROM @SearchKeyWords.nodes('/Parent/Child') AS SearchKeyWords(SearchKeyword)

SELECT SUM(SearchKeyWord) FROM @TempSearchKeyWords

Answer 4:

你在这里的XML是非类型化XML。 这意味着提供给值sum()的类型为xdt:untypedAtomic 。 当sum()被用在xdt:untypedAtomic的值被转换为xs:double 。 的结果sum()由读values()函数作为字符串（或untypedAtomic类型）和xs:double使用科学记数法，当值小于1.0E6，或者大于或等于1.0E6。 SQL Server不能从科学记数法转换int或bigint 。

参考：
总和函数（的XQuery）
XQuery中的类型转换规则

其他的答案已经提供了投输入值或从结果的解决方法sum()或使用float在作为数据类型values() 另一种选择是使用类型化XML来代替。

架构：

create xml schema collection XSDSumTest as '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Parent">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="Child" maxOccurs="unbounded">
                    <xs:complexType>
                        <xs:simpleContent>
                            <xs:extension base="xs:int">
                                <xs:attribute name="ID" type="xs:int"/>
                            </xs:extension>
                        </xs:simpleContent>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
            <xs:attribute name="ID" type="xs:string"/>
        </xs:complexType>
    </xs:element>
</xs:schema>'

查询：

declare @xml xml(XSDSumTest) = '
<Parent ID="p">
  <Child ID="1">1000000000</Child > 
  <Child ID="2">234650</Child > 
  <Child ID="3">0</Child > 
</Parent>'

select @xml.value('sum(/Parent[@ID="p"]/Child)','bigint') as Sum

文章来源: strange behavior of SQL Server when sum nodes's values in XML