[SQL] SQL 서버 : 포함 된 VARCHAR (MAX) 필드에서 유효하지 않은 XML 문자를 교체
SQLSQL 서버 : 포함 된 VARCHAR (MAX) 필드에서 유효하지 않은 XML 문자를 교체
나는 XML 형식으로 외부 시스템에 인터페이스되고있는 VARCHAR (MAX) 필드가 있습니다. 다음 오류는 인터페이스에 의해 발생했다 :
mywebsite.com-2015-0202.xml:413005: parser error : xmlParseCharRef: invalid xmlChar value 29
ne and Luke's family in Santa Fe. You know you have a standing invitation,
^
mywebsite.com-2015-0202.xml:455971: parser error : xmlParseCharRef: invalid xmlChar value 25
The apprentice nodded, because frankly, who hadnt? That diseases like chol
^
mywebsite.com.com-2015-0202.xml:456077: parser error : xmlParseCharRef: invalid xmlChar value 28
bon mot; a sentimental love of nature and animals; the proverbial British 
^
mywebsite.com-2015-0202.xml:472073: parser error : xmlParseCharRef: invalid xmlChar value 20
"Andyou want that?"
^
mywebsite.com-2015-0202.xml:492912: parser error : xmlParseCharRef: invalid xmlChar value 25
She couldnt live like this anymore.
우리는 다음 문자 목록이 유효하지 않은 것으로 나타났습니다 :
�








	
























나는이 데이터를 정리하려고, 나는 여기에 이러한 문자를 청소하는 SQL 함수를 발견했다. I 대신 VARCHAR (MAX)를 사용하여 기능을 변경되도록하지만, 함수는 입력 매개 변수로 (4000) NVARCHAR 복용 하였다.
VARCHAR (MAX)에 NVARCHAR (4000) 잘못된 결과를 얻을 것이다 변경하는 경우 사람이 조언을 주시겠습니까? 미안 해요, 로컬 그래서 의견을 / 조언을 추구하는 생각이 인터페이스를 테스트 할 수 없습니다.
원래 기능 :
CREATE FUNCTION fnStripLowAscii (@InputString nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
IF @InputString IS NOT NULL
BEGIN
DECLARE @Counter int, @TestString nvarchar(40)
SET @TestString = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) + ']%'
SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
WHILE @Counter <> 0
BEGIN
SELECT @InputString = STUFF(@InputString, @Counter, 1, NCHAR(164))
SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
END
END
RETURN(@InputString)
END
버전 수정 된 항목 :
CREATE FUNCTION [dbo].RemoveInvalidXMLCharacters (@InputString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
IF @InputString IS NOT NULL
BEGIN
DECLARE @Counter INT, @TestString NVARCHAR(40)
SET @TestString = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) + ']%'
SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
WHILE @Counter <> 0
BEGIN
SELECT @InputString = STUFF(@InputString, @Counter, 1, ' ')
SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
END
END
RETURN(@InputString)
END
해결법
-
==============================
1.base64로 다시에 VARBINARY의 암시 적 변환을 사용하여 트릭이있다 :
base64로 다시에 VARBINARY의 암시 적 변환을 사용하여 트릭이있다 :
악의 목록 여기
DECLARE @evilChars VARCHAR(MAX)= CHAR(0x0) + CHAR(0x1) + CHAR(0x2) + CHAR(0x3) + CHAR(0x4) + CHAR(0x5) + CHAR(0x6) + CHAR(0x7) + CHAR(0x8) + CHAR(0x9) + CHAR(0xa) + CHAR(0xb) + CHAR(0xc) + CHAR(0xd) + CHAR(0xe) + CHAR(0xf) + CHAR(0x10) + CHAR(0x11) + CHAR(0x12) + CHAR(0x13) + CHAR(0x14) + CHAR(0x15) + CHAR(0x16) + CHAR(0x17) + CHAR(0x18) + CHAR(0x19) + CHAR(0x1a) + CHAR(0x1b) + CHAR(0x1c) + CHAR(0x1d) + CHAR(0x1e) + CHAR(0x1f) + CHAR(0x7f);이 작품
DECLARE @XmlAsString NVARCHAR(MAX)= ( SELECT @evilChars FOR XML PATH('test') ); SELECT @XmlAsString;그 결과 (일부는 "인쇄"있다)
<test>� 
</test>다음은 금지됩니다
SELECT CAST(@XmlAsString AS XML)하지만 당신은 64 기수로 VARBINARY의 암시 적 변환을 사용할 수 있습니다
DECLARE @base64 NVARCHAR(MAX)= ( SELECT CAST(@evilChars AS VARBINARY(MAX)) FOR XML PATH('test') ); SELECT @base64;결과
<test>AAECAwQFBgcICQoLDA0ODxAREhMUFRYXGBkaGxwdHh9/</test>지금 당신은 특수 문자를 포함하여 실제 XML있어!
SELECT CAST(CAST(@base64 AS XML).value('/test[1]','varbinary(max)') AS VARCHAR(MAX)) FOR XML PATH('reconverted')결과
<reconverted>�  </reconverted> -
==============================
2.VARCHAR (MAX) 필드 내 데이터 열로 VARCHAR (MAX)를 사용하는 것이 안전합니다. I는 NVARCHAR (MAX) PARAM을 허용 SQL 함수에 VARCHAR (MAX) 필드를 전달하는 경우도, NVARCHAR (MAX)에 VARCHAR (MAX)을 전환의 오버 헤드가된다.
VARCHAR (MAX) 필드 내 데이터 열로 VARCHAR (MAX)를 사용하는 것이 안전합니다. I는 NVARCHAR (MAX) PARAM을 허용 SQL 함수에 VARCHAR (MAX) 필드를 전달하는 경우도, NVARCHAR (MAX)에 VARCHAR (MAX)을 전환의 오버 헤드가된다.
당신의 의견을 당신에게 @Damien_The_Unbeliever을 매우 @RhysJones 감사드립니다.
-
==============================
3.대신 VARCHAR (최대)의 사용 NVARCHAR (최대) 필요하지만, 그렇지 않으면 변화는 미세이다.
대신 VARCHAR (최대)의 사용 NVARCHAR (최대) 필요하지만, 그렇지 않으면 변화는 미세이다.
from https://stackoverflow.com/questions/28365316/sql-server-replace-invalid-xml-characters-from-a-varcharmax-field by cc-by-sa and MIT license
'SQL' 카테고리의 다른 글
| [SQL] 어떻게 SQL에서 필드 값 당 행의 수를 제한 할 수 있습니까? (0) | 2020.06.12 |
|---|---|
| [SQL] 여러 행을 업데이트 할 때 NULL 유형을 주조 (0) | 2020.06.12 |
| [SQL] MySQL의보기에서 행 순위 (0) | 2020.06.12 |
| [SQL] 오라클 : 기능 인덱스 선택의 고유성 (0) | 2020.06.11 |
| [SQL] SQL 그룹으로 각 그룹 상위 N 항목 (0) | 2020.06.11 |