[MSSQL]FOR XML PATH 사용시CHAR(13) 문자열이  로 바뀌는 현상 해결 방법

Database/SQL Server(MSSQL)

[MSSQL]FOR XML PATH 사용시CHAR(13) 문자열이  로 바뀌는 현상 해결 방법

금백조 2021. 10. 25. 23:05

서론

FOR XML PATH와 STUFF를 이용하여 행 문자열들을 하나의 문자로 합치고 각 문자 사이에 개행 문자를 추가하려는 중 개행 문자 일부인 CHAR(13) 문자열이 문자열로 변환되는 현상이 나타났습니다. 이를 문자열 치환을 통해 해결한 방법을 소개하려 합니다.

실행환경

MSSQL 13.0 - CHAR(13) 문자열이 로 바뀌는 현상 발생
MSSQL 15.0 - CHAR(13) 문자열이 로 바뀌는 현상 발생하지 않음. 정상 작동 확인

본론

[상황]

FOR XML PATH, STUFF를 이용하여 문자열 합치기를 수행 중 CHAR(13) 문자열이 문자열로 변환됨.

SELECT STUFF((SELECT ',' + EMP_NO+'('+EMP_NM+')'
FROM(
	SELECT 'E0001' EMP_NO, 'ALICE' EMP_NM
	UNION ALL
	SELECT 'E0002' EMP_NO, 'BOB' EMP_NM
	UNION ALL
	SELECT 'E0003' EMP_NO, 'SAM' EMP_NM
	UNION ALL
	SELECT 'E0004' EMP_NO, 'TOM' EMP_NM
	UNION ALL
	SELECT 'E0005' EMP_NO, 'SWAN' EMP_NM
)EMP
FOR XML PATH('')),1,1,'') AS EMP_LIST

[문제]

CHAR(13) 문자열이 로 바뀌는 현상

SELECT CHAR(13)+ CHAR(10) + EMP_NO+'('+EMP_NM+')'--개행 : CHAR(13)+ CHAR(10)
FROM(
	SELECT 'E0001' EMP_NO, 'ALICE' EMP_NM
	UNION ALL
	SELECT 'E0002' EMP_NO, 'BOB' EMP_NM
	UNION ALL
	SELECT 'E0003' EMP_NO, 'SAM' EMP_NM
	UNION ALL
	SELECT 'E0004' EMP_NO, 'TOM' EMP_NM
	UNION ALL
	SELECT 'E0005' EMP_NO, 'SWAN' EMP_NM
)EMP
FOR XML PATH('')

[해결책]

CHAR(13) 문자열을 #CHAR13#로 치환 후 FOR XML PATH가 끝나면 REPLACE 함수를 통해 #CHAR13#을 CHAR(13) 문자열로 다시 바꿔준다.

SELECT STUFF(REPLACE(
(SELECT '#CHAR13#' + CHAR(10) + EMP_NO+'('+EMP_NM+')'
FROM(
	SELECT 'E0001' EMP_NO, 'ALICE' EMP_NM
	UNION ALL
	SELECT 'E0002' EMP_NO, 'BOB' EMP_NM
	UNION ALL
	SELECT 'E0003' EMP_NO, 'SAM' EMP_NM
	UNION ALL
	SELECT 'E0004' EMP_NO, 'TOM' EMP_NM
	UNION ALL
	SELECT 'E0005' EMP_NO, 'SWAN' EMP_NM
)EMP
FOR XML PATH('')),'#CHAR13#', CHAR(13)),1,2,'') EMP_LIST