MsSQL xml parse to decimal

问题:

I'm processing xml with this stored procedure

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)

The problem is GrossValue, which can be empty

<GrossValue></GrossValue>

and probably when it is, I get an error

Error converting data type nvarchar to numeric

How can I prepare it to get 0.0 instead of error?

Edit:

I came up with

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

but it is ugly as hell

回答1:

Long before TRY_CAST and TRY_CONVERT were invented there was an XQuery cast:

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

Using cast as xs:decimal? will return a NULL when the value is not castable.

In order to get 0.0 instead of a NULL you might use ISNULL()-function (as pointed out by TT. already).

Just as a hint:

In older versions, were the TRY_ calls do not work, this was a good workaround:

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

回答2:

You can try get a VARCHAR value from the element, use TRY_CAST to decimal, and use ISNULL to get 0.0 if the value is NULL.

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

About as ugly I suppose.