Post reply

Select decimal value from XML generated with XSINIL

  • 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)

  • August 2, 2007 at 10:00 am

    #724246

    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]

  • August 3, 2007 at 6:36 am

    #724477

    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.

     

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

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