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

서론

 

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

 

 

결론

 

SQL Server 버전이 비교적 오래된 버전일 경우 FOR XML PATH 사용 시 CHAR(13) 문자열이 
 로 바뀌는 현상이 발생할 수 있는 것 같습니다. 따라서 위와 같은 현상이 발생할 경우 문자열 치환을 통해 해결하시면 되겠습니다.

 

Reference

Microsoft Docs - STUFF(Transact-SQL)

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver15