[MS-SQL] JSON 데이터를 xml데이터로 파싱하는 함수를 사용하여 DB에 저장하는 방법
다음 함수는 JSON데이터 타입으로 프론트엔드쪽에서 넘겨준 JSON데이터 타입의 데이터를 MSSQL에서 XML데이터로 파싱하는 함수 입니다. 테이블 반환 함수는 아니며, 스칼라 반환 함수입니다.
그럼 이런 건 언제 활용하느냐?
프론트엔드쪽에서 테이블구조의 데이터를 JSON 타입으로 데이터를 넘긴 후 DB에 저장할 때 활용하면 굉장히 유용합니다. 보통 아이바티스나 마이바티스와 같은 ORM를 사용과 백엔드 코드를 작성하여 저장하면 코드 구현시 문제될 게 없지만 모든 저장 비즈니스 로직을 프로시저에서 구현해서 저장해야한다면 아주 유용합니다.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION FUN_PARSE_JSON_TO_XML(
@json nvarchar(max)
)
RETURNS xml
AS
BEGIN;
DECLARE @output varchar(max), @key varchar(max), @value varchar(max),
@recursion_counter int, @offset int, @nested bit, @array bit,
@tab char(1)=CHAR(9), @cr char(1)=CHAR(13), @lf char(1)=CHAR(10);
--- Clean up the JSON syntax by removing line breaks and tabs and
--- trimming the results of leading and trailing spaces:
SET @json=LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(@json, @cr, ''), @lf, ''), @tab, '')));
--- Sanity check: If this is not valid JSON syntax, exit here.
IF (LEFT(@json, 1)!='{' OR RIGHT(@json, 1)!='}')
RETURN '';
--- Because the first and last characters will, by definition, be
--- curly brackets, we can remove them here, and trim the result.
SET @json=LTRIM(RTRIM(SUBSTRING(@json, 2, LEN(@json)-2)));
SELECT @output='';
WHILE (@json!='') BEGIN;
--- Look for the first key which should start with a quote.
IF (LEFT(@json, 1)!='"')
RETURN 'Expected quote (start of key name). Found "'+
LEFT(@json, 1)+'"';
--- .. and end with the next quote (that isn't escaped with
--- and backslash).
SET @key=SUBSTRING(@json, 2,
PATINDEX('%[^\]"%', SUBSTRING(@json, 2, LEN(@json))+' "'));
--- Truncate @json with the length of the key.
SET @json=LTRIM(SUBSTRING(@json, LEN(@key)+3, LEN(@json)));
--- The next character should be a colon.
IF (LEFT(@json, 1)!=':')
RETURN 'Expected ":" after key name, found "'+
LEFT(@json, 1)+'"!';
--- Truncate @json to skip past the colon:
SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));
--- If the next character is an angle bracket, this is an array.
IF (LEFT(@json, 1)='[')
SELECT @array=1, @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));
IF (@array IS NULL) SET @array=0;
WHILE (@array IS NOT NULL) BEGIN;
SELECT @value=NULL, @nested=0;
--- The first character of the remainder of @json indicates
--- what type of value this is.
--- Set @value, depending on what type of value we're looking at:
---
--- 1. A new JSON object:
--- To be sent recursively back into the parser:
IF (@value IS NULL AND LEFT(@json, 1)='{') BEGIN;
SELECT @recursion_counter=1, @offset=1;
WHILE (@recursion_counter!=0 AND @offset<LEN(@json)) BEGIN;
SET @offset=@offset+
PATINDEX('%[{}]%', SUBSTRING(@json, @offset+1,
LEN(@json)));
SET @recursion_counter=@recursion_counter+
(CASE SUBSTRING(@json, @offset, 1)
WHEN '{' THEN 1
WHEN '}' THEN -1 END);
END;
SET @value=CAST(
dbo.fn_parse_json2xml(LEFT(@json, @offset))
AS varchar(max));
SET @json=SUBSTRING(@json, @offset+1, LEN(@json));
SET @nested=1;
END
--- 2a. Blank text (quoted)
IF (@value IS NULL AND LEFT(@json, 2)='""')
SELECT @value='', @json=LTRIM(SUBSTRING(@json, 3,
LEN(@json)));
--- 2b. Other text (quoted, but not blank)
IF (@value IS NULL AND LEFT(@json, 1)='"') BEGIN;
SET @value=SUBSTRING(@json, 2,
PATINDEX('%[^\]"%',
SUBSTRING(@json, 2, LEN(@json))+' "'));
SET @json=LTRIM(
SUBSTRING(@json, LEN(@value)+3, LEN(@json)));
END;
--- 3. Blank (not quoted)
IF (@value IS NULL AND LEFT(@json, 1)=',')
SET @value='';
--- 4. Or unescaped numbers or text.
IF (@value IS NULL) BEGIN;
SET @value=LEFT(@json,
PATINDEX('%[,}]%', REPLACE(@json, ']', '}')+'}')-1);
SET @json=SUBSTRING(@json, LEN(@value)+1, LEN(@json));
END;
--- Append @key and @value to @output:
SET @output=@output+@lf+@cr+
REPLICATE(@tab, @@NESTLEVEL-1)+
'<'+@key+'>'+
ISNULL(REPLACE(
REPLACE(@value, '"', '"'), '\', ''), '')+
(CASE WHEN @nested=1
THEN @lf+@cr+REPLICATE(@tab, @@NESTLEVEL-1)
ELSE ''
END)+
'</'+@key+'>';
--- And again, error checks:
---
--- 1. If these are multiple values, the next character
--- should be a comma:
IF (@array=0 AND @json!='' AND LEFT(@json, 1)!=',')
RETURN @output+'Expected "," after value, found "'+
LEFT(@json, 1)+'"!';
--- 2. .. or, if this is an array, the next character
--- should be a comma or a closing angle bracket:
IF (@array=1 AND LEFT(@json, 1) NOT IN (',', ']'))
RETURN @output+'In array, expected "]" or "," after '+
'value, found "'+LEFT(@json, 1)+'"!';
--- If this is where the array is closed (i.e. if it's a
--- closing angle bracket)..
IF (@array=1 AND LEFT(@json, 1)=']') BEGIN;
SET @array=NULL;
SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));
--- After a closed array, there should be a comma:
IF (LEFT(@json, 1) NOT IN ('', ',')) BEGIN
RETURN 'Closed array, expected ","!';
END;
END;
SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)+1));
IF (@array=0) SET @array=NULL;
END;
END;
--- Return the output:
RETURN CAST(@output AS xml);
END다음과 같은 객체 타입의 데이터를 JSON.stringify()를 이용하여 json타입의 문자열로 넘겨줍니다.
sampleData = {}
sampleData ={"CUST_EXP":"10","CUST_BEFORE_SW":"30","CUST_AGE":"","CUST_TYPE":"10"};
makeJson() {
return JSON.stringify(sampleData);
},json데이터로 변환된 데이터는 아래와 같아요.
"PARAMS1":"{"HCT_001":"10","HCT_003":"112","MEL_002":"ㄱㅎㄹㅇㄹㅇ","OLM_001":"30"}"
,"PARAMS2":"{"HOSTPT_TP":"10","BEFORE_SW_TP":"40"}"
,"PARAMS3":"{"CUST_EXP":"10","CUST_BEFORE_SW":"30","CUST_AGE":"10","CUST_TYPE":"30"}"저장 프로시저에서 호출하는 방법은 다음과 같습니다.
@P_COMP_INFOS NVARCHAR(MAX)
@P_PROD_INFOS NVARCHAR(MAX)
@P_PROD_INFOS_DATA NVARCHAR(MAX)
DECLARE @xmlOjbect XML;
SELECT @xmlOjbect = DBO.FUN_PARSE_JSON_TO_XML(@P_COMP_INFOS);
SET @xmlOjbect = '<ROOT>' + CONVERT(VARCHAR(MAX), @xmlOjbect) + '</ROOT>';
UPDATE TB_COMP
SET HOSTPT_TP = @xmlOjbect.value('(/ROOT/HOSTPT_TP)[1]', 'int'),
BEFORE_SW_TP = @xmlOjbect.value('(/ROOT/BEFORE_SW_TP)[1]', 'int')
WHERE STATUS = @P_STATUS AND COMP_ID = @P_COMP_ID
INSERT INTO TB_ACT_EXT (STATUS, COMP_ID, ACT_ID, HOSTPT_TP, BEFORE_SW_TP)
VALUES (@P_STATUS
, @P_COMP_ID
, @P_ACT_ID
, @xmlOjbect.value('(/ROOT/HOSTPT_TP)[1]', 'int')
, @xmlOjbect.value('(/ROOT/BEFORE_SW_TP)[1]', 'int'));
SELECT @xmlOjbect = DBO.FUN_PARSE_JSON_TO_XML(@P_EXT_CONT_INFOS);
SET @xmlOjbect = '<ROOT>' + CONVERT(VARCHAR(MAX), @xmlOjbect) + '</ROOT>';
UPDATE TB_CUSTOMER
SET CUST_EXP = @xmlOjbect.value('(/ROOT/CUST_EXP)[1]', 'int'),
CUST_BEFORE_SW = @xmlOjbect.value('(/ROOT/CUST_BEFORE_SW)[1]', 'int'),
CUST_AGE = @xmlOjbect.value('(/ROOT/CUST_AGE)[1]', 'int'),
CUST_TYPE = @xmlOjbect.value('(/ROOT/CUST_TYPE)[1]', 'int')
WHERE STATUS = @P_STATUS AND CONT_ID = @P_CONT_ID;
INSERT INTO TB_ACTION_CUSTOMER_EXT (STATUS, CONT_ID, ACT_ID, CUST_EXP, CUST_BEFORE_SW, CUST_AGE, CUST_TYPE)
VALUES (
@P_STATUS
, @P_CONT_ID
, @P_ACT_ID
, @xmlOjbect.value('(/ROOT/CUST_EXP)[1]', 'int')
, @xmlOjbect.value('(/ROOT/CUST_BEFORE_SW)[1]', 'int')
, @xmlOjbect.value('(/ROOT/CUST_AGE)[1]', 'int')
, @xmlOjbect.value('(/ROOT/CUST_TYPE)[1]', 'int')
);
INSERT INTO TB_ACTION_EXT (STATUS, ACT_ID, VAL_KEY, EXT_ATTR_VAL)
SELECT @P_STATUS,
@P_ACT_ID,
C.value('local-name(.)', 'NVARCHAR(30)') as VAL_KEY,
C.value('(.)[1]', 'NVARCHAR(4000)') as VAL_1
FROM @xmlOjbect.nodes(N'/*') as T(C);
SELECT @xmlOjbect = DBO.FUN_PARSE_JSON_TO_XML(@P_PROD_INFOS);
INSERT INTO TB_ACTION_EXT (STATUS, ACT_ID, VAL_KEY, EXT_ATTR_VAL)
SELECT @P_STATUS,
@P_ACT_ID,
C.value('local-name(.)', 'NVARCHAR(30)') as VAL_KEY,
C.value('(.)[1]', 'NVARCHAR(4000)') as VAL_1
FROM @xmlOjbect.nodes(N'/*') as T(C);;
SELECT @xmlOjbect = DBO.FUN_PARSE_JSON_TO_XML(@P_PROD_INFOS_DATA);
CREATE TABLE #TEMP_PROD_TABLE (
ITEM_ID VARCHAR(30),
);
INSERT INTO TB_ACTION_HISTORY (STATUS, ITEM_ID, CONT_ID, ACT_ID, VAL_KEY, EXT_ATTR_VAL, CRT_USER, CRT_TIME)
SELECT @P_STATUS,
CC.ITEM_ID,
@P_CONT_ID,
@P_ACT_ID,
C.value('local-name(.)', 'NVARCHAR(30)') as VAL_KEY,
C.value('(.)[1]', 'NVARCHAR(4000)') as VAL_1,
@P_USER_ID,
GetDATE()
FROM @xmlOjbect.nodes(N'/*') as T(C)
JOIN TB_COMMON_CODE CC ON CC.STATUS = @P_STATUS AND CC.VAL_KEY = C.value('local-name(.)', 'NVARCHAR(30)')
INSERT INTO #TEMP_PROD_TABLE(ITEM_ID)
SELECT CC.ITEM_ID
FROM @xmlOjbect.nodes(N'/*') as T(C)
JOIN TB_COMMON_CODE CC ON CC.STATUS = @P_STATUS AND CC.VAL_KEY = C.value('local-name(.)', 'NVARCHAR(30)')
GROUP BY CC.ITEM_ID
INSERT INTO TB_ACTION_INFO (STATUS, ITEM_ID, CONT_ID, ACT_ID, VAL_KEY, EXT_ATTR_VAL)
SELECT @P_STATUS,
CC.ITEM_ID,
@P_CONT_ID,
@P_ACT_ID,
C.value('local-name(.)', 'NVARCHAR(30)') as VAL_KEY,
C.value('(.)[1]', 'NVARCHAR(4000)') as VAL_1
FROM @xmlOjbect.nodes(N'/*') as T(C)
JOIN TB_COMMON_CODE CC ON CC.STATUS = @P_STATUS AND CC.VAL_KEY = C.value('local-name(.)', 'NVARCHAR(30)')백엔드(서버사이드)단에서 처리하는 방법외에 이런 방식으로 데이터를 저장할 수 있다는 것을 오늘 하나 배웁니다.
