SQLTeam.com | Weblogs | Forums

CROSS APPLY with OPENJSON


#1

Hello all,

I have a table where one of the field (Employee.D) returned kind value [{"X":2.0000000e+000,"Y":6.3000}]

So, when I run my select statement, I am getting this error of

"Conversion failed when converting the nvarchar value '2.0000000e+000' to data type int."

My working progress query:

WITH CTEA AS (SELECT A,B,C,D FROM TABLE_EMPLOYEE)

SELECT Employee.A, Employee.B, Employee.C, Employee.D
FROM CTEA Employee
CROSS APPLY OPENJSON (Employee.D) WITH (X INT, Y NUMERIC(10.4))

How do I get rid of this JSON error? Anyone?


#2

That's not a JSON error. You're trying to convert a string ("2.000000e+000") to an int. Try to convert to a float in between (CONVERT(float, X)

I'd test it but don't have access to a SQL Server with JSON support at the moment


#3

Thank you gbritton