I'm trying to convert my JSON data into a table format in SQL Server. Following are my JSON data:
[{
"emp_no": "001",
"emp_designation":"Data Admin",
"emp_name": "Peter",
"emp_name2": "彼特"
},
{
"emp_no": "002",
"emp_designation":"Software Engineer",
"emp_name": "Lee",
"emp_name2": "李"
}]
What I had tried are:
DECLARE @JSON NVARCHAR(MAX)
set @JSON='[{
"emp_no": "001",
"emp_designation":"Data Admin",
"emp_name": "Peter",
"emp_name2": "彼特"},
{
"emp_no": "002",
"emp_designation":"Software Engineer",
"emp_name": "Lee",
"emp_name2": "李"
}]'
--Method 1
SELECT * INTO #emp_temp FROM OPENJSON(@JSON)
WITH (emp_no varchar(20),
emp_designation varchar(50),
emp_name NVARCHAR(100),
emp_name2 NVARCHAR(100))
SELECT * FROM #Emp_temp
DROP TABLE #Emp_temp
--Method 2
SELECT
JSON_Value (EMP.VALUE, '$.emp_no') as emp_no,
JSON_Value (EMP.VALUE, '$.emp_designation') as emp_designation,
JSON_Value (EMP.VALUE, '$.emp_name') as emp_name,
JSON_Value (EMP.VALUE, '$.emp_name2') as emp_name2
INTO #Emp_temp2
FROM OPENJSON (@JSON) as EMP
SELECT * FROM #Emp_temp2
DROP TABLE #Emp_temp2
However, both temp table return me following result, with the Chinese characters remain as "???". Temp table select result
emp_no emp_designation emp_name emp_name2
001 |Data Admin | Peter| ??
002 |Software Engineer| Lee | ?
Any idea how to preserve the original Chinese characters after parse the data into temp table?
Thanks.
*Edit: I know it can work by putting a extra 'N' in front of the JSON
set @JSON=N'[
{ "emp_no": "001...
.....
But actually the JSON is a parameter in a Store Procedure, I cannot simply add a N like : set @JSON = 'N' + @JSON, which this will jeopardize the format of the JSON data, and cause an error.
ALTER PROCEDURE [dbo].[SP_StoreEmpInfo]
@JSON NVARCHAR(max)
@JSON = 'N' + @JSON
/*Will cause invalid JSON format error */
SELECT
JSON_Value (EMP.VALUE, '$.emp_no') as.....
