0

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.....

1 Answer 1

Reset to default
2

Try adding 'N' before your sql set to indicate that unicode characters are contained within like this:

DECLARE @JSON NVARCHAR(MAX)

set @JSON=N'[{
"emp_no": "001",
"emp_designation":"Data Admin",
"emp_name": "Peter",
"emp_name2": "彼特"},
 {
"emp_no": "002",
"emp_designation":"Software Engineer",
"emp_name": "Lee",
"emp_name2": "李"
 }]'

This question may assist in background: What does N' stands for in a SQL script ? (the one used before characters in insert script)

6
  • Sorry that I do not mention clearly, actually my JSON is a parameter inside a Stored Procedure. I cannot simply put an 'N' in front of it, if I try to do something like this: set JSON = 'N' + JSON I will hit an error, since the N will cause the an error of invalid JSON format.
    – Chee Hou Ng
    Commented Oct 20, 2017 at 4:01
  • @CheeHouNg How do you call the stored procedure? Show the code.
    – Tomalak
    Commented Oct 20, 2017 at 7:41
  • @Tomalak Just a normal c# code: var serializer = new JavaScriptSerializer(); var JsonString = serializer.Serialize(List_EmpInfo); string connS = ConfigurationManager.ConnectionStrings["DB_Connn"].ConnectionString; SqlConnection connection = new SqlConnection(connS); SqlCommand cmd = new SqlCommand("SP_StoreEmpInfo", connection) { CommandType = CommandType.StoredProcedure }; cmd.Parameters.AddWithValue("@Json", JsonString);
    – Chee Hou Ng
    Commented Oct 20, 2017 at 10:24
  • sorry, I'm new at here, the code might look dirty, because I have no idea how to do newline in SO's comment section.
    – Chee Hou Ng
    Commented Oct 20, 2017 at 10:28
  • 1
    @Tomalak it works with the following code cmd.Parameters.Add("@JsonString", SqlDbType.NVarChar, -1); Thanks.
    – Chee Hou Ng
    Commented Oct 24, 2017 at 10:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.