question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked ErlandSommarskog commented

XML to table amount value format issue

Hi,

I have xml like below.
<Activity>
<ID>2000790721500-85</ID>
<Net>6.00000000000001E-02</Net>
</Activity>

i try to read xml into table like below
SELECT
ID = CL.Activity.value('(ID)[1]', 'varchar(50)'),
Net = CL.Activity.value('(Net)[1]', 'decimal(18,3)')
FROM
@XML.nodes('/Advice/Activity') CL(Activity);

in that xml the 'Net' value is some thing different(6.00000000000001E-02) which am receiving. while i read that value i face the below exception.

Error converting data type nvarchar to numeric.

how to fix that problem

Thanks
Kasim

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

If it must be decimal(18,3), then try something like this:

 SELECT
    ID = CL.Activity.value('(ID)[1]', 'varchar(50)'),
    Net = cast(CL.Activity.value('Net[1]', 'float') as decimal(18,3))
 FROM @XML.nodes('/Advice/Activity') CL(Activity)



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ErlandSommarskog commented

Maybe you can try real instead of decimal(18, 3):

 DECLARE @XML XML = '
 <Activity>
 <ID>2000790721500-85</ID>
 <Net>6.00000000000001E-02</Net>
 </Activity>
 ';
    
 SELECT
     ID = CL.Activity.value('(ID)[1]', 'varchar(50)'),
     Net = CL.Activity.value('(Net)[1]', 'real')
 FROM
     @XML.nodes('/Activity') CL(Activity);
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Both real and float are problematic here, although real is a lot more problematic.

The target type is decimal(18,2). This means 18 digits of precision. With float, you get a precision of 14-15 digits. With real, 7-8 or so.

Look at this:

DECLARE @XML XML = '
<Activity>
<ID>2000790721500-85</ID>
<Net>1234567890123456.78</Net>
</Activity>
';
   
SELECT
    ID = CL.Activity.value('(ID)[1]', 'varchar(50)'),
    Net = cast(CL.Activity.value('(Net)[1]', 'float') AS decimal(18,2))
FROM
    @XML.nodes('/Activity') CL(Activity);

The value returned is 1234567890123456.75 which I will have to say is surprisingly good.

Now, this does not have to be an issue. If the source comes with numbers in scientific notation, we can suspect that the source uses float, and in that case there is no 18 digits of precision.

But don't use real. With real instead of float above, the value is 1234567948140544.00 - a far cry from the original.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ErlandSommarskog commented

Hi @kasimmohamed-6993,

Here is a clean way to handle your data.

6.00000000000001E-02 is the same as 6.00000000000001 * (10 to the power of negative 2) = 0.0600000000000001

Useful links:
scientific-notation
xs:double Datatype Reference
type-casting-rules-in-xquery

SQL

 DECLARE @XML XML = '
 N<Activity>
     <ID>2000790721500-85</ID>
     <Net>6.00000000000001E-02</Net>
 </Activity>';
        
 -- 6.00000000000001E-02 is the same as 6.00000000000001 * (10 to the power of negative 2) = 0.0600000000000001
 SELECT ID = CL.Activity.value('(ID/text())[1]', 'varchar(50)')
     , Net = CL.Activity.value('(Net/text())[1] cast as xs:double?', 'DECIMAL(20,16)')
 FROM @XML.nodes('/Activity') AS CL(Activity);

Output

 +------------------+--------------------+
 |        ID        |        Net         |
 +------------------+--------------------+
 | 2000790721500-85 | 0.0600000000000001 |
 +------------------+--------------------+

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yithzak, that method appears to have some shortcomings:

DECLARE @XML XML = '
 N<Activity>
     <ID>2000790721500-85</ID>
     <Net>1234567890123456.78</Net>
 </Activity>';
        
 -- 6.00000000000001E-02 is the same as 6.00000000000001 * (10 to the power of negative 2) = 0.0600000000000001
 SELECT ID = CL.Activity.value('(ID/text())[1]', 'varchar(50)')
     , Net = CL.Activity.value('(Net/text())[1] cast as xs:double?', 'DECIMAL(20,16)')
 FROM @XML.nodes('/Activity') AS CL(Activity);

Results in:

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

I think the problem is that while you cast to double in the XQuery, it is still converted to text when it comes out to T-SQL.

0 Votes 0 ·

@ErlandSommarskog,

My answer is applicable just for a scenario with the scientific notation.

When it is a regular value - no need to do any casting inside the XPath/XQuery expression.

 DECLARE @XML2 XML = 
 'N<Activity>
     <ID>2000790721500-85</ID>
     <Net>1234567890123456.78</Net>
 </Activity>';
    
 SELECT ID = CL.Activity.value('(ID/text())[1]', 'VARCHAR(50)')
     , Net = CL.Activity.value('(Net/text())[1]', 'DECIMAL(20,2)')
 FROM @XML2.nodes('/Activity') AS CL(Activity);


0 Votes 0 ·

V My answer is applicable just for a scenario with the scientific notation.

My assumption is that the poster needs a solution that works well with any value that the source can produce. So we cannot have different methods for different values.

0 Votes 0 ·