MsSQL xml 解析为十进制

Question

我正在用这个存储过程处理 xml

select
col.query('./AgreementId').value('.','uniqueidentifier') as [AgreementId],
x.query('./GrossValue').value('.','decimal(19, 4)') as [GrossValue]
into #AdvanceInvoices
from @XML.nodes('/DataFromERP/CustomObjects/Agreements/Agreement') as ref(col)
cross apply ref.col.nodes('AdvanceInvoices/AdvanceInvoice') as T(x)

问题是 GrossValue，它可以是空

的

<GrossValue></GrossValue>

可能是当它是时，我收到一个错误

Error converting data type nvarchar to numeric

如何准备它以获得 0.0 而不是错误？

编辑：

我想出了

CAST(COALESCE(NULLIF(ISNULL(x.query('./GrossValue').value('.','nvarchar(50)'),''),''),'0.0') as decimal(19,4)) as [GrossValue],

但它丑陋如地狱

Answer 1

早在TRY_CAST和TRY_CONVERT被发明之前，就有一个XQuery的演员表：

DECLARE @SomeTable TABLE(ID INT IDENTITY, SomeXML XML);
INSERT INTO @SomeTable VALUES
(N'<DATA><SomeNumber>1</SomeNumber></DATA>')     --integer
,(N'<DATA><SomeNumber>1.1</SomeNumber></DATA>')   --decimal
,(N'<DATA><SomeNumber></SomeNumber></DATA>')      --empty
,(N'<DATA><SomeNumber>abc</SomeNumber></DATA>')   --invalid
,(N'<DATA><SomeNumber>.123</SomeNumber></DATA>')  --short
SELECT t.SomeXML.value(N'/DATA[1]/SomeNumber[1]/text()[1] cast as xs:decimal?',N'decimal(19,4)')
FROM @SomeTable AS t

当值不可强制转换时，使用cast as xs:decimal?将返回一个NULL。

为了获得0.0而不是NULL，您可以使用ISNULL()函数(正如TT已经指出的那样)。

只是作为一个提示：

在旧版本中，如果TRY_调用不起作用，这是一个很好的解决方法：

DECLARE @InvalidNumber VARCHAR(100)='123a'
,@ValidNumber VARCHAR(100)='123';
SELECT (SELECT @InvalidNumber FOR XML PATH(''),TYPE).value(N'. cast as xs:int?',N'int') AS InvalidNumber
,(SELECT @ValidNumber FOR XML PATH(''),TYPE).value(N'. cast as xs:int?',N'int') AS ValidNumber;

甚至更简单，只是一个演员表：

DECLARE @InvalidNumber VARCHAR(100)='123a'
,@ValidNumber VARCHAR(100)='123';
SELECT CAST(@InvalidNumber AS XML).value(N'. cast as xs:int?',N'int') AS InvalidNumber
,CAST(@ValidNumber AS XML).value(N'. cast as xs:int?',N'int') AS ValidNumber

Answer 2

您可以尝试从元素中获取 VARCHAR 值，使用 TRY_CAST 到十进制，如果值为 NULL，则使用 ISNULL 获取 0.0。

ISNULL(TRY_CAST(x.query('./GrossValue').value('.','VARCHAR(20)') AS DECIMAL(19,4)),.0) AS [GrossValue]

我想和丑陋差不多。