webentwicklung-frage-antwort-db.com.de

SQL Server: Ersetzen Sie ungültige XML-Zeichen aus einem VARCHAR-Feld (MAX)

Ich habe ein VARCHAR(MAX)-Feld, das im XML-Format an ein externes System angeschlossen wird. Die folgenden Fehler wurden von der Schnittstelle ausgelöst:

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.

Wir haben festgestellt, dass die folgende Liste von Zeichen ungültig ist:

�








	

























Ich versuche, diese Daten zu säubern, und ich habe eine SQL-Funktion gefunden, um diese Zeichen hier zu säubern. Die Funktion nahm jedoch NVARCHAR(4000) als Eingabeparameter, daher habe ich die Funktion geändert, um stattdessen VARCHAR(MAX) zu verwenden. 

Könnte jemand bitte raten, wenn die Änderung von NVARCHAR(4000) in VARCHAR(MAX) zu falschen Ergebnissen führen würde? Es tut mir leid, ich könnte dieses Interface nicht lokal testen, also habe ich gedacht, um Meinungen/Ratschläge einzuholen.

Ursprüngliche Funktion:

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

Modifizierte Version:

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
7
Sathish

Es ist sicher, VARCHAR(MAX) zu verwenden, da meine Datenspalte ein VARCHAR(MAX)-Feld ist. Es wird auch ein Aufwand für die Konvertierung von VARCHAR(MAX) in NVARCHAR(MAX) entstehen, wenn ich ein VARCHAR(MAX)-Feld an die SQL-Funktion übergeben, die den Parameter NVARCHAR(MAX) akzeptiert. 

Vielen Dank @RhysJones, @Damien_The_Unbeliever für Ihre Kommentare.

1
Sathish

Es gibt einen Trick, der die implizite Konvertierung von VARBINARY in base64 und zurück verwendet:

Hier deine Liste des Bösen

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);

Das funktioniert

DECLARE @XmlAsString NVARCHAR(MAX)=
(
    SELECT @evilChars FOR XML PATH('test')
);
SELECT @XmlAsString;

Das Ergebnis (einige werden "gedruckt")

<test>&#x00;&#x01;&#x02;&#x03;&#x04;&#x05;&#x06;&#x07;&#x08;    
&#x0B;&#x0C;&#x0D;&#x0E;&#x0F;&#x10;&#x11;&#x12;&#x13;&#x14;&#x15;&#x16;&#x17;&#x18;&#x19;&#x1A;&#x1B;&#x1C;&#x1D;&#x1E;&#x1F;</test>

Folgendes ist verboten

SELECT CAST(@XmlAsString AS XML)

Sie können jedoch die implizite Konvertierung von VARBINARY in base64 verwenden

DECLARE @base64 NVARCHAR(MAX)=
(
    SELECT CAST(@evilChars AS VARBINARY(MAX)) FOR XML PATH('test')
);
SELECT @base64;

Das Ergebnis

<test>AAECAwQFBgcICQoLDA0ODxAREhMUFRYXGBkaGxwdHh9/</test>

Jetzt haben Sie Ihr echtes XML mit den Sonderzeichen !

SELECT CAST(CAST(@base64 AS XML).value('/test[1]','varbinary(max)') AS VARCHAR(MAX)) FOR XML PATH('reconverted')
7
Shnugo

Sie müssen nvarchar(max) anstelle von varchar(max) verwenden, ansonsten ist die Änderung in Ordnung.

0
Rhys Jones