In einem Projekt sammele ich per SQLCMD Daten und speichere diese in XML-Dateien. Mit SQL Server 2005, 2008 und 2008 R2 funktioniert dies einwandfrei.
Ab SQL Server 2012 jedoch speichert SQLCMD nicht immer ein valides XML-Dokument.
Der Grund sind Zeilenumbrüche. Die XML-Datei enthält nach jedem 2034. Zeichen einen Zeilenumbruch.
Fällt auch nur einer der Zeilenumbrüche in einen Wert oder einen Tag, ist das XML-Dokument nicht mehr valide.
Um dieses Dilemma nachvollziehen zu können, beginnen wir mit einem Skript, dass eine Datenbank wie auch eine Beispieltabelle namens xmlKunden anlegt und diese mit 25.000 Datensätzen füllt.
— Datenbank anlegen
CREATE DATABASE xmlTest;
GO
USE xmlTest;
GO
— Tabelle und Daten anlegen
SET NOCOUNT ON;
— Variablen zum Erstellen von Daten
DECLARE @intZaehler int = 0, @strZaehler nvarchar(19);
— Bereits bestehende Tabelle löschen
IF Object_ID(‚xmlKunden‘) Is Not Null
BEGIN
DROP TABLE dbo.xmlKunden;
END
— Tabelle erstellen
CREATE TABLE dbo.xmlKunden
(
ID int IDENTITY(1,1),
Matchcode nvarchar(8),
Firma nvarchar(255),
Strasse nvarchar(256),
Plz nvarchar(8),
Ort nvarchar(256)
);
— Tabelle mit 25.000 Datensätzen füllen
WHILE @intZaehler < 25000
BEGIN
SET @intZaehler = @intZaehler + 1;
SET @strZaehler = CAST(@intZaehler As nvarchar(19));
INSERT INTO dbo.xmlKunden (Matchcode, Firma, Strasse, Plz, Ort)
VALUES (‚KD‘ + @strZaehler, ‚Firma‘ + @strZaehler,
‚Strasse‘ + @strZaehler, ‚P-‚ + @strZaehler, ‚Ort‘ + @strZaehler);
END
GO
Der Inhalt der Beispieltabelle soll per SQLCMD als XML-Dokument exportiert werden. Hierzu benötigen wir folgendes Skript:
SET NOCOUNT ON;
:XML ON
SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
(SELECT ID, Matchcode, Firma, Strasse, Plz, Ort
FROM xmlTest.dbo.xmlKunden Kunde
FOR XML AUTO, TYPE)
FOR XML RAW (‚Kunden‘);
Die Datenermittlung per FOR XML liefert ein XML-Dokument, dass durch den Zusatz :XML ON als XML-Datenstrom ausgegeben wird.
Der Zusatz :XML ON funktioniert nur mit SQLCMD. Das SQL Server Management Studio quittiert die Verwendung von :XML ON mit einer Fehlermeldung.
Die Skriptdatei speichern wir als xmlSelectKunden.sql und starten dann in der Eingabeaufforderung den Datenexport mit folgendem Befehl:
SQLCMD -S <SqlServer> -i „D:\xmlSelectKunden.sql“ -o „D:\Kunden.xml“
Das Ergebnis ist ein XML-Dokument mit Zeilenumbrüchen. Die Statuszeile zeigt den Zeilenumbruch an der 2034. Stelle.
Dieses Verhalten ist netterweise bei Microsoft Connect unter dem Eintrag http://connect.microsoft.com/SQLServer/feedback/details/786004/sqlcmd-with-xml-on-break-lines-on-large-output beschrieben.
Der Eintrag bezieht sich zwar nur auf den Export per SQLCMD mit der Anweisung :XML ON, das Verhalten gilt jedoch ebenso für den Export eines XML-Dokuments per BCP. Mehr zu BCP folgt weiter unten.
Microsoft Connect hat den Eintrag bereits geschlossen und als nicht lösbar gekennzeichnet.
Es wird lediglich auf die dort aufgeführten Workarounds hingewiesen. Zum heutigen Zeitpunkt sind dies nur zwei.
Ein Workaround empfiehlt die Installation von SQLCMD in der Version vom SQL Server 2008 R2 auf SQL Server ab der Version 2012.
Dies hätte ein inhomogenes System zur Folge, was wohl in den meisten Fällen vom Administrator nicht toleriert wird.
Der zweite Workaround empfiehlt, das Ergebnis der Datenermittlung zunächst in einer Variablen vom Datentyp nvarchar(max) zu speichern und anschließend den Wert der Variablen per SELECT auszugeben.
Ok, dann ändern wir halt das Skript …
SET NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
(SELECT ID, Matchcode, Firma, Strasse, Plz, Ort
FROM xmlTest.dbo.xmlKunden Kunde
FOR XML AUTO, TYPE)
FOR XML RAW (‚Kunden‘));
:XML ON
SELECT @Xml As Ausgabe;
… und führen es erneut aus.
SQLCMD -S <SqlServer> -i „D:\xmlSelectKunden.sql“ -o „D:\Kunden.xml“
Und siehe da “ die XML-Datei enthält nun keine unerwünschten Zeilenumbrüche.
Dafür ist sie nun auf 2.049 Zeichen begrenzt. Es bleibt also bei einem nicht validen XML-Dokument.
Vielleicht hilft ja eine Anpassung des Workarounds. Wie sieht das Ergebnis aus, wenn der Export ohne den Zusatz :XML ON erfolgt?
Schließlich geben wir aktuell per SELECT lediglich eine Zeichenfolge aus.
Das lässt sich schnell herausfinden: Die Zeile mit :XML ON auskommentieren, das Skript speichern und per SQLCMD ein weiteres Mal ausführen.
SET NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
(SELECT ID, Matchcode, Firma, Strasse, Plz, Ort
FROM xmlTest.dbo.xmlKunden Kunde
FOR XML AUTO, TYPE)
FOR XML RAW (‚Kunden‘));
–:XML ON
SELECT @Xml As Ausgabe;
SQLCMD -S <SqlServer> -i „D:\xmlSelectKunden.sql“ -o „D:\Kunden.xml“
Mit diesem Skript liefert SQLCMD eine einfache Zeichenfolge. Das Ergebnis ist somit keine XML-Datei mehr, sondern eine Textdatei.
Mit einigen Einschränkungen, denn zum einen enthält die Ausgabe eine Kopfzeile und zum anderen ist die Breite der jeweiligen Spalten auf 256 Zeichen begrenzt.
Die Textdatei kann auch eine Informationsmeldung über einen Datenbankwechsel enthalten, wenn dieser im Skript mit dem USE-Befehl erfolgt.
Dies lässt sich vermeiden, wenn die Tabelle mit dem vollqualifizierten Namen bestehend aus Datenbank, Schema und Tabellenname angesprochen wird.
Die Ausgabe der Kopfzeile wie auch die Begrenzung der Ausgabe auf 256 Zeichen kann ebenfalls vermieden werden.
Hierfür gibt es die beiden folgenden Parameter:
-h-1 vermeidet die Ausgabe der Kopfzeile
-y0 erweitert die maximale Größe einer Spalte auf 1 MB
Die Grenze von 1 MB ist nicht fix. Sie lässt sich mit einer Zahl beliebig festlegen. Der Wert 0 steht hier für den maximalen Wert “ und der liegt nun mal bei 1 MB.
Speichern wir dieses Ergebnis nun in einer Datei mit der Erweiterung xml, erhalten wir ein valides XML-Dokument “ vorausgesetzt der Inhalt ist nicht größer als 1 MB.
Um dies zu testen, schränken wir die Datenermittlung per TOP-Klausel auf 10 Datensätze ein.
SET NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
(SELECT TOP 10 ID, Matchcode, Firma, Strasse, Plz, Ort
FROM xmlTest.dbo.xmlKunden Kunde
FOR XML AUTO, TYPE)
FOR XML RAW (‚Kunden‘));
–:XML ON
SELECT @Xml As Ausgabe;
Nachdem wir das Skript gespeichert haben, ergänzen wir den Aufruf von SQLCMD mit den Parametern und starten den Datenexport.
SQLCMD -S <SqlServer> -i „D:\xmlSelectKunden.sql“ -o „D:\Kunden.xml“ -h-1 -y0
Das Ergebnis ist ein valides XML-Dokument.
Entfernen wir im Skript die TOP-Klausel, erhalten wir ein Ergebnis von mehr als 1 MB.
Dann wird die Ausgabe einfach abgeschnitten “ und schon haben wir wieder kein valides XML-Dokument.
Vielleicht liegt es ja am Datentyp der Variablen, die das Ergebnis der Datenermittlung speichert. Denn wir wollten ja eigentlich ein XML-Dokument und keine Zeichenfolge.
Ändern wir doch einfach mal den Datentyp der Variablen von nachvar(max) auf xml.
Das Ergebnis ist das gleiche. Die Ausgabe ist auf 1 MB begrenzt. Daran ändert sich auch nichts, wenn das Skript wieder mit dem Zusatz :XML ON ausgeführt wird.
Der Grund liegt in der maximal möglichen Breite einer Spalte.
Als Alternative zu SQLCMD ist vielleicht BCP eine Hilfe. Da sich mit BCP jedoch keine Skripte ausführen lassen, brauchen wir für die Datenermittlung eine Sicht, eine Gespeicherte Prozedur oder eine Tabellenwertfunktion. Versuchen wir es mit einer Gespeicherten Prozedur.
USE xmlTest;
GO
CREATE PROC dbo.pExportKundenXml
AS
SET NOCOUNT ON;
SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
(SELECT ID, Matchcode, Firma, Strasse, Plz, Ort
FROM xmlTest.dbo.xmlKunden Kunde
FOR XML AUTO, TYPE)
FOR XML RAW (‚Kunden‘);
In der Eingabeaufforderung starten wir dann den Datenexport per BCP mit diesen Parametern:
BCP xmlTest.dbo.pExportKundenXml queryout „D:\Kunden.xml“ -S <SqlServer> -T -x „c
Das Ergebnis ist ernüchternd. Die XML-Datei enthält wieder die unerwünschten Zeilenumbrüche.
Naja, dann nutzen wir doch den Workaround vom SQLCMD und schreiben das Ergebnis zunächst in eine Variable vom Datentyp nvarchar(max), um diese per SELECT auszugeben.
USE xmlTest;
GO
ALTER PROC dbo.pExportKundenXml
AS
SET NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT ‚Datum = ‚ + CONVERT(nvarchar(23),Getdate(), 127) As Datum,
(SELECT ID, Matchcode, Firma, Strasse, Plz, Ort
FROM xmlTest.dbo.xmlKunden Kunde
FOR XML AUTO, TYPE)
FOR XML RAW (‚Kunden‘));
SELECT @Xml As Ausgabe;
Starten wir also den Datenexport per BCP ein weiteres Mal.
BCP xmlTest.dbo.pExportKundenXml queryout „D:\Kunden.xml“ -S <SqlServer> -T -x „c
Fantastisch “ endlich haben wir eine XML-Datei ohne die lästigen Zeilenumbrüche und somit ein valides XML-Dokument.
Sogar die Ausgabe ist nicht auf 1 MB begrenzt.
Der für SQLCMD beschriebene Workaround liefert beim Datenexport per BCP das erwartete Ergebnis.
Dabei ist es gleich, ob die Variable vom Datentyp xml oder nvarchar(max) ist.
Der Nachteil dieser Variante liegt jedoch darin, dass wir an einer Gespeicherten Prozedur oder einer Tabellenwertfunktion mit mehreren Anweisungen nicht vorbeikommen. Nun kann es ja durchaus sein, dass das Erstellen eigener Datenbankobjekte nicht erwünscht ist. In diesem Fall ist der Datenexport per BCP nicht möglich. Also schauen wir nochmal nach einer Lösung per SQLCMD.
Bei den bisherigen Datenexporten per SQLCMD wird das XML-Dokument entweder als Datenstrom oder als Zeichenfolge in einer einzelnen Spalte ausgegeben. Beim Datenstrom per :XML ON erhalten wir die Zeilenumbrüche und bei der Ausgabe in einer Spalte ist diese auf 1 MB begrenzt.
Die Begrenzung der Ausgabe auf 1 MB gilt jedoch nur für eine Spalte einer Zeile.
Warum also das Ergebnis nicht auf mehrere Zeilen verteilen?
Schreiben wir doch einfach die einzelnen Datensätze vom Ergebnis nicht per FOR XML in ein XML-Dokument, sondern zeilenweise als Zeichenfolgen in eine Tabelle. Diese Datensätze ergänzen wir mit weiteren Datensätzen, die das Start- und Ende-Tag des XML-Dokuments enthalten. Somit ergibt der Inhalt der Tabelle das eigentliche XML-Dokument.
Das Ergebnis könnte jedoch Zeichen enthalten, die für ein XML-Dokument reserviert sind. Hierzu gehören neben dem Hochkomma und dem Anführungszeichen auch die Zeichen &, < und >. Um nicht mühsam diese Zeichen per REPLACE mit den Maskierungen wie ‚“‚ ersetzen zu müssen, nutzen wir einfach wieder FOR XML für die Datenermittlung. Auf diese Weise erhalten wir ein valides XML-Dokument.
Das XML-Dokument speichern wir in einer Variable vom Datentyp xml und lesen dann die einzelnen Zeilen per XQuery aus. Dabei konvertieren wir jede einzelne Zeile in eine Zeichenfolge. Hinzu kommt eine weitere Spalte namens Satzart, die den Wert ‚D‘ für „Daten“ erhält.
Die so aufbereiteten Zeilen schreiben wir per SELECT INTO in eine Tabelle.
SELECT INTO wird an dieser Stelle nicht ohne Grund verwendet: Es ist am schnellsten.
Anschließend ergänzen wir die erstellte Tabelle mit einer weiteren Spalte namens „EintragID“. Diese Spalte erhält die IDENTITY-Funktion und vergibt somit den einzelnen Datensätzen eine fortlaufende Nummer.
Nun fehlen noch die Einträge für die Start- und Ende-Tags des XML-Dokuments.
Das Start-Tag schreiben wir mit der Satzart „A“ für „Anfang“ in die Tabelle und das Ende-Tag mit der Satzart „E“ für „Ende“.
Die Satzart dient lediglich zur Sortierung der Ausgabe, denn die folgt per ORDER BY nach den Spalten „Satzart“ und „EintragID“.
Durch diese Sortierung sind die Start-Tags am Anfang (Satzart „A“), gefolgt von den Daten (Satzart „D“) und dem Ende-Tag (Satzart „E“).
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
— XML-Variable
DECLARE @xmlErgebnis xml;
— Ergebnis als XML aufbereiten
SET @xmlErgebnis = (SELECT ID, Matchcode, Firma, Strasse, Plz, Ort
FROM xmlTest.dbo.xmlKunden Kunde
FOR XML AUTO, TYPE);
— Ergebnis Zeile für Zeile ergänzt mit Satzart in Tabelle speichern
SELECT CAST(‚D‘ As char(1)) As Satzart,
CAST(Zeile.Wert.query(‚.‘) As nvarchar(max)) As Zeile
INTO #tabXML
FROM @xmlErgebnis.nodes(‚/Kunde‘) Zeile(Wert);
— Tabelle mit laufender Nummer erweitern
ALTER TABLE #tabXML ADD ZeilenNr int IDENTITY(1,1);
— Start-Tag einfügen
INSERT INTO #tabXml (Satzart, Zeile)
VALUES (‚A‘, ‚<Kunden Datum=“‚ + CONVERT(nvarchar(19), GETDATE(), 127) + ‚“>‘);
— Ende-Tag einfügen
INSERT INTO #tabXml (Satzart, Zeile) VALUES (‚E‘, ‚</Kunden>‘);
— Ausgabe der Daten – sortiert nach Satzart (A, D, E) und Zeilennummer
SELECT Zeile FROM #tabXML ORDER BY Satzart, ZeilenNr;
— Tabelle wieder löschen
DROP TABLE #tabXML
Dieses Skript speichern wir unter der Bezeichnung xmlSelectKunden.sql.
Dann exportieren wir die Daten der Beispieltabelle per SQLCMD in eine Datei mit der Erweiterung xml.
Wobei wir hier wieder die Parameter -h-1 und -y0 verwenden, um die Ausgabe der Kopfzeile zu vermeiden und die maximale Spaltenbreite auf 1 MB festzulegen. Zusätzlich kommt nun noch der Parameter -u hinzu. Hiermit erhalten wir eine Unicode-Datei.
SQLCMD -S <SqlServer> -i „D:\xmlSelectKunden.sql“ -o „D:\Kunden.xml“ -h-1 -y0 -u
Die so erstellte Textdatei beinhaltet ein valides XML-Dokument und lässt sich durch die Erweiterung xml auch als solches verwenden.
