Verhalten von FOR XML PATH und Sonderzeichen &

[ukulele]

Ich habe eines meiner Scripte zum Konvertieren von Daten von vor ein oder zwei Jahren genommen und für einen neuen Zweck leicht angepasst. In mehreren Schritten zerlege ich dabei eine Zeile aus einer CSV-Datei und baue sie später mit FOR XML PATH wieder zusammen. Der Grund sind nicht escapte Trennzeichen (Semikolon), spielt aber eigentlich für mein Problem keine Rolle.

Ich habe das auf einem SQL 2019 Developer geschrieben, lief noch auf einem Windows 2012 R2. Die VM gibt es nicht mehr, ich kann das leider nicht prüfen. Aktuell führe ich mein Script auf Server 2022 mit SQL 2022 aus und laufe in ein Problem, das früher eigentlich so nicht dagewesen sein kann. Ich kann mir nicht vorstellen, das sich die beiden Systeme unterschiedlich verhalten aber ich kann mir auch nicht erklären, warum mir das beim ersten mal nicht zum Verhängnis geworden ist. Auch finde ich das Verhalten allgemein etwas merkwürdig und vor allem unerwartet an dieser Stelle.

Hier mal eine Beschränkung auf das absolut Wesentliche:

CREATE DATABASE test;

USE test;

SELECT    DATABASEPROPERTYEX('test', 'Collation') AS DefaultCollation;

CREATE TABLE t(
    line SMALLINT,
    [value] NVARCHAR(MAX),
    value_number SMALLINT
    );

INSERT INTO t VALUES (1,N'&',1),(1,N'b',2),(1,N'c',3);

SELECT    *
FROM    t;

/*
1    &    1
1    b    2
1    c    3
*/

SELECT    stuff((    SELECT    '' + t.[value]
                FROM    t
                ORDER BY t.value_number
                FOR XML PATH('')),1,0,'');

/*
&bc
*/

DROP TABLE t;

CREATE TABLE t(
    line SMALLINT,
    [value] VARCHAR(MAX),
    value_number SMALLINT
    );

INSERT INTO t VALUES (1,'&',1),(1,'b',2),(1,'c',3);

SELECT    *
FROM    t;

/*
1    &    1
1    b    2
1    c    3
*/

SELECT    stuff((    SELECT    '' + t.[value]
                FROM    t
                ORDER BY t.value_number
                FOR XML PATH('')),1,0,'');

/*
&bc
*

stuff() und FOR XML PATH bauen einfach nur die verschiedenen Werte wieder zusammen zu einem String. Dabei wird aus dem &-Zeichen ein & und das sorgt im späteren Verlauf für Probleme. Natürlich kann ich & jetzt wieder durch & ersetzen bevor ich weiter mache - nicht so schön aber praktikabel. Nur ist mir das vorher nie aufgefallen und ich verstehe auch den Grund nicht. Ich dachte erst, es liegt an der COLLATION oder an NVARCHAR(), damit hat man ja beim Import immer seinen Ärger. Es scheint aber immer aufzutreten, egal was ich da verwende.

Kann mir jemand das Verhalten erklären oder eine Quelle nennen, die das bestätigt? Ist das einfach immer so?[/CODE]

 

[dabadepdu]

SELECT '' + t.[value]

Das würde ich mal unter die Lupe nehmen und schauen, welchen Typ die engine dafür ausspuckt.
Bin kein ms Entwickler, aber der Typ wird u. U. anhand des ersten 'atoms' des Ausdrucks gebaut.

Vielleicht sind es auch Stuff-Effekte, oder sogar Client encoding (für Terminal, DOS Box oder so)... keine Ahnung

 

[ukulele]

Ja das hatte ich auch schon mal mit COLLATE explizit bestimmt, hier nochmal mit einer Variable die vom Typ her identisch zur Collation der DB und der Spalte ist.

DECLARE    @var NVARCHAR(MAX) = '' COLLATE Latin1_General_CI_AS

SELECT    stuff((    SELECT    @var + t.[value]
                FROM    t
                ORDER BY t.value_number
                FOR XML PATH('')),1,0,'');

Ändert nichts am Ergebnis. Die Collation scheint eigentlich nicht das Problem, es scheint wirklich immer bei FOR XML PATH so zu laufen.

 

[t-sql]

Vermutlich liegt es einfach daran, das in XML die Notation für & &amp ist.

 

[ukulele]

Vermutlich liegt es einfach daran, das in XML die Notation für & &amp ist.

Das ist mir natürlich klar. Ich habe allerdings nicht damit gerechnet das Zeichen konvertiert werden, wenn ich die Funktion aufrufe. Passiert übrigens auch mit anderen Zeichen wie > und <. Es muss sich eigentlich auf dem 2019 SQL genauso verhalten haben, es hat sich nur anders ausgewirkt - ist wohl "richtig" so.

 

[t-sql]

Das ist mir natürlich klar. Ich habe allerdings nicht damit gerechnet das Zeichen konvertiert werden, wenn ich die Funktion aufrufe. Passiert übrigens auch mit anderen Zeichen wie > und <. Es muss sich eigentlich auf dem 2019 SQL genauso verhalten haben, es hat sich nur anders ausgewirkt - ist wohl "richtig" so.

Also, da ich das Problem interessant finde hab ich das jetzt mal auf SQL Server 2022, 2019 und 2017 dein Beispiel ausgeführt. Dreimal das gleiche Ergebnis. Was mich aber bei STUFF() stutzig gemacht hat, ist die Längenangabe mit 0. Wenn ich jetzt ein

select stuff('&amp', 1, 0, '');

ausführe passiert nichts. Da ja 0 ersetzt wird.

 

[ukulele]

Eigentlich müsste es
&amp;
sein, aber was genau möchtest du erreichen? stuff() verändert den String nicht - FOR XML PATH verändert die Zeichen.

FOR XML PATH wird halt schon länger gerne als Ersatz für z.B. GROUP_CONCAT() eingesetzt, weil etwas vergleichbares bei MSSQL fehlt. Es gibt gar nicht zwingend einen XML-Bezug sondern einfach nur Bedarf für eine elegante Lösung, die auch out of the box funktioniert. Ich habe bisher an keiner Stelle eine genaue Erklärung oder auch nur einen Hinweis gefunden, was es eigentlich genau alles tut.

 

[t-sql]

SELECT    stuff((    SELECT    @var + t.[value]
                FROM    t
                ORDER BY t.value_number
                FOR XML PATH('')),1,0,'');

Deswegen versteh ich den Teil hier nicht. Faktisch passiert ja da gar nichts, weil stuff() nichts ersetzt. FOR XML, egal was danach kommt, gibt halt das Ergebnis als XML zurück (siehe hier Using the FOR XML Clause to Return Query Results as XML - Simple Talk). Aber was du suchst ist die Funktion STRING_AGG(), gibts seit SQL Server 2017.

 

[ukulele]

Stimmt, stuff() ist aus dem ursprünglichen Kontext genommen und überflüssig. STRING_AGG(), sieht gut aus, das könnte ich vermutlich nehmen, werde ich testen. Habe allerdings auch noch SQL 2014 in zwei Fällen.