SQL 쿼리 결과를 XML 형태로 출력 할 때 아래와 같은 오류가 발생하는 경우가 있다.
FOR XML could not serialize the data for node ‘[ Column name]’ because it contains a character (0x001F) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
XML로 변활 할 대상 데이터에 ASCII control code가 포함되어 있어서 발생하는 경우이다. 그래서 대상 데이터 안에 포함 되어 있는 해당 아래 스크립트 같이 XML로 변환 전에 모두 치환해 주어야 한다.
SELECT
REPLACE([Column name], char(0), '')
FROM
[Table Name]
Control code를 삭제하는 Function을 만들어 두면 필요할 때 가져다 쓰면 좋다.
CREATE FUNCTION [dbo].[FN_ReplaceControlCharacter]
(
@mString NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @mString = REPLACE(@mString, char(0), '')
SET @mString = REPLACE(@mString, char(1), '')
SET @mString = REPLACE(@mString, char(2), '')
SET @mString = REPLACE(@mString, char(3), '')
SET @mString = REPLACE(@mString, char(4), '')
SET @mString = REPLACE(@mString, char(5), '')
SET @mString = REPLACE(@mString, char(6), '')
SET @mString = REPLACE(@mString, char(7), '')
SET @mString = REPLACE(@mString, char(8), '')
SET @mString = REPLACE(@mString, char(9), '')
SET @mString = REPLACE(@mString, char(10), '')
SET @mString = REPLACE(@mString, char(11), '')
SET @mString = REPLACE(@mString, char(12), '')
SET @mString = REPLACE(@mString, char(13), '')
SET @mString = REPLACE(@mString, char(14), '')
SET @mString = REPLACE(@mString, char(15), '')
SET @mString = REPLACE(@mString, char(16), '')
SET @mString = REPLACE(@mString, char(17), '')
SET @mString = REPLACE(@mString, char(18), '')
SET @mString = REPLACE(@mString, char(19), '')
SET @mString = REPLACE(@mString, char(20), '')
SET @mString = REPLACE(@mString, char(21), '')
SET @mString = REPLACE(@mString, char(22), '')
SET @mString = REPLACE(@mString, char(23), '')
SET @mString = REPLACE(@mString, char(24), '')
SET @mString = REPLACE(@mString, char(25), '')
SET @mString = REPLACE(@mString, char(26), '')
SET @mString = REPLACE(@mString, char(27), '')
SET @mString = REPLACE(@mString, char(28), '')
SET @mString = REPLACE(@mString, char(29), '')
SET @mString = REPLACE(@mString, char(30), '')
SET @mString = REPLACE(@mString, char(31), '')
RETURN @mString
END
0부터 31까지 순차적으로 있어서 While Loop를 사용하면 간결하게 보일 수 있긴 하지만, 데이터가 많은 경우(ex 100만개 이상의 row) 2배 이상의 속도 차이가 날 수 있다. 성능을 고려해야 한다면, 최대한 필요한 Control code를 특정 지어서 직접 치환하는 것을 권장한다.
THE NEWTH 