Firstly i obatin data from Excel and convert them into DataTable in C# project.
Secondly i parse this DataTable into JSON string and send it to database as a stored procedure parameter.
I want to perform the merge operation on some table with values from this JSON string parameter. Values from this parameter can be represented as such table:
DECLARE @JsonData NVARCHAR(MAX);
SET @JsonData = N'[
{"id": 1, "lval": "-10;15", "hval": "-20;45", "unit": "kg;m"}
]';
DECLARE @ExampleTable TABLE (EQ BIGINT, L_VALUE NVARCHAR(100), H_VALUE NVARCHAR(100), UNIT NVARCHAR (30))
INSERT INTO @ExampleTable
SELECT *
FROM OPENJSON(@JsonData) WITH (
[EQ] BIGINT 'strict $.id',
[L_VALUE] NVARCHAR(100) '$.lval',
[H_VALUE] NVARCHAR(100) '$.hval',
[UNIT] NVARCHAR(20) '$.unit')
SELECT * FROM @ExampleTable
| EQ (ID) | L_VALUE (nvarchar(100)) | H_VALUE (nvarchar(100)) | UNIT(nvarchar(30)) |
|---|---|---|---|
| 1 | -10;15 | -20;45 | kg;m |
The first value (from either L_VALUE or H_VALUE) before the semicolon stands for the first unit from the UNIT column, second stands for second unit etc… There can be more pairs of values and units or there can be simply one value one unit so in example:
| EQ (ID) | L_VALUE (nvarchar(100)) | H_VALUE (nvarchar(100)) | UNIT(nvarchar(30)) |
|---|---|---|---|
| 1 | 3;21;-19 | 2;11;-5 | kg;cm;ml |
or
| EQ (ID) | L_VALUE (nvarchar(100)) | H_VALUE (nvarchar(100)) | UNIT(nvarchar(30)) |
|---|---|---|---|
| 1 | 10 | -2 | cm |
I would like to obtain the result in the table form as follows:
| EQ (ID FK) | L_VALUE (float) | H_VALUE (float)) | UNIT (nvarchar(30)) |
|---|---|---|---|
| 1 | -10 | -20 | kg |
| 1 | 15 | 45 | m |
Advertisement
Answer
You may try to transform the values in the L_VALUE, H_VALUE and UNIT columns as JSON (-10;25 into ["-10", "-25"]) and parse the values with additional OPENJSON() call. The result from the second OPENJSON() is a table with columns key, value and type and in case of an array, the key column contains the index of each item in the JSON array, so you need an appropriate JOINs:
Table and JSON:
DECLARE @JsonData NVARCHAR(MAX);
SET @JsonData = N'[
{"id": 1, "lval": "-10;15", "hval": "-20;45", "unit": "kg;m"},
{"id": 2, "lval": "-10;15;13", "hval": "-20;45;55", "unit": "kg;m;cm"},
{"id": 3, "lval": "-10", "hval": "-20", "unit": "kg"}
]';
DECLARE @ExampleTable TABLE (
EQ BIGINT,
L_VALUE NVARCHAR(100),
H_VALUE NVARCHAR(100),
UNIT NVARCHAR (30)
)
Statement:
INSERT INTO @ExampleTable
SELECT j.[EQ], a.[L_VALUE], a.[H_VALUE], a.[UNIT]
FROM OPENJSON(@JsonData) WITH (
[EQ] BIGINT 'strict $.id',
[L_VALUE] NVARCHAR(100) '$.lval',
[H_VALUE] NVARCHAR(100) '$.hval',
[UNIT] NVARCHAR(20) '$.unit'
) j
CROSS APPLY (
SELECT l.[value], h.[value], u.[value]
FROM OPENJSON(CONCAT('["', REPLACE(j.L_VALUE, ';', '","'), '"]')) l
JOIN OPENJSON(CONCAT('["', REPLACE(j.H_VALUE, ';', '","'), '"]')) h ON l.[key] = h.[key]
JOIN OPENJSON(CONCAT('["', REPLACE(j.UNIT, ';', '","'), '"]')) u ON l.[key] = u.[key]
) a (L_VALUE, H_VALUE, UNIT)
Result:
EQ L_VALUE H_VALUE UNIT ---------------------- 1 -10 -20 kg 1 15 45 m 2 -10 -20 kg 2 15 45 m 2 13 55 cm 3 -10 -20 kg