Select decimal value from XML generated with XSINIL

Question

Select decimal value from XML generated with XSINIL

aarded

Ten Centuries

Points: 1312
More actions
July 30, 2007 at 2:53 am

#175674

The select of a decimal value, from an XML string, generated with XSINIL attribute, and with NULL for the decimal value, generates the error 'Error converting data type nvarchar to numeric'.
Replacing 'dec' with 'int' works fine.
Does someone have a solution to extract the value as decimal?
-- Example:
DECLARE @xBody XML
SELECT @xBody=
'<Info xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><ID>1739583</ID><lAmount xsi:nil="true" /></Info>'
-- Works fine.
select Message.value('ID[1]', 'int') AS ID,
Message.value('lAmount[1]', 'int') AS lBundle
FROM @xBody.nodes('/Info') AS x (Message)
-- ERROR: Error converting data type nvarchar to numeric.
select Message.value('ID[1]', 'int') AS ID,
Message.value('lAmount[1]', 'dec(18,6)') AS lBundle
FROM @xBody.nodes('/Info') AS x (Message)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

Hi,

When querying the XML, SQL Server does not see a NULL value produced by xsi:nil; the value is interpreted as not being there at all rather than a NULL value. An ugly workaround would be to retrieve the lAmount value as a varchar and then query the result. Something like:

SELECT ID, CASE WHEN LEN(lAmount) > 0 THEN CAST(lBundle AS DECIMAL(16,8)) ELSE 0.0 END

FROM(

select Message.value('ID[1]', 'int') AS ID,

Message.value('lAmount[1]', 'varchar(10)') AS lBundle

FROM @xBody.nodes('/Info') AS x (Message)

)xmlResult

Hope this helps!

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

aarded Ten Centuries Points: 1312 More actions · Answer 2

The workaround:

SELECT

Message.value('ID[1]', 'int') AS ID,

CASE WHEN LEN(Message.value('lAmount[1]', 'VARCHAR'))=0

THEN 0 ELSE Message.value('lAmount[1]', 'dec(18,6)') END AS lBundle

FROM @xBody.nodes('/Info') AS x (Message)

works fine.

Stupid, but who cares.

tnx.