对 XML 中的节点值求和时 SQL Server 的奇怪行为

2021-10-01 00:00:00 xml sql sql-server-2008 sql-server

我问了一个关于 sum 节点值的问题:

I ask a question about sum node's values:

总结 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

如果你执行这个它会重新运行这个错误:

if you execute this it retrun this error:

Msg 8114, Level 16, State 5, Line 8将数据类型 nvarchar 转换为 bigint 时出错.

Msg 8114, Level 16, State 5, Line 8 Error converting data type nvarchar to bigint.

问题是它返回这个值:1.00023465E9

如果我以这种方式更改上述查询就可以了:

if I change the above query this way it being ok:

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 这样做?

Why Sql Server do this?

推荐答案

Sql Server 在将带有科学记数法的值从字符串转换为整数时出现问题，这在您运行 xpath 查询时会发生，但是，它可以做到这是 float.

Sql Server has a problem converting the value with scientific notation from a string to an integer, as would happen when you run your xpath query, however, it can do this for float.

您可以这样编写查询:

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

相关文章