XML und T-SQL: eine Geschichte mit vielen Mißverständnissen
Es ist problemlos möglich aus einer XML-Datei mit einem definierten Schema ein relationales Dataset zu erzeugen, genauso ist es möglich das Ergebnis einer Abfrage, also ein relationale Resultset in einer XML-codierten Ansicht wieder auszugeben und ggf. an weitere selbstprogrammierte Schnittstellen zu übertragen um einen Export in Fremdsysteme zu ermöglichen. Was aber scheinbar nicht so einfach möglich ist, ist der direkte Export in eine Datei mit gültigem XML-Code zum Inhalt.
In diesem und dem nachfolgenden Artikel werden wir einerseits betrachten, welche Lösungen als Vorschläge im Internet im Umlauf sind und deren Schwächen herausstellen sowie eine Lösung anbieten, die allerdings erst ab SQL-Server 2012 nutzbar ist, da die Voraussetzungen hierfür erst mit dieser Version in das Featureset aufgenommen wurden. (Dafür funktioniert der angebotene Weg auch mit der Standardversion sowie ab SQL-Server 2016 auch für JSON-Dateien.)
Wenn es darum geht das XML-codierte Resultset einer Abfrage ohne Zuhilfenahme von Drittanbieter-Software in Dateiform zu speichern, gibt es laut einschlägigen Quellen im Internet insgesamt drei Wege, die hier kurz dargelegt werden und deren Schwächen diskutiert werden sollen:
1. SQL-Server-Mangement-Studio
2. Direkter Export via BCP
3. Verwendung einer Zwischentabelle gefolgt von einem Export via BCP
Schauen wir uns diese Wege einmal genauer an. Wir beginnen mit der offensichtlichsten Variante – dem Export mittels SSMS:
Hier kann man einfach eine Abfrage schreiben, die das Ergebnis in einer XML-Codierung darstellt und die resultierende XML-Datei einfach abspeichern.
Die Zieldatei soll hier den Namen XMLExport1.xml tragen.
Als Beispiel verwende ich die TSQL2012 Datenbank von Itzik Ben Gan um folgendes Statement abzusetzen:
1: select
2: [Customer].custid as [custid],
3: [Customer].companyname as [companyname],
4: [Order].orderid as [orderid],
5: [Order].orderdate as [orderdate],
6: [OrderPosition].unitprice as [unitprice],
7: [OrderPosition].qty as [quantity],
8: ([OrderPosition].unitprice * [OrderPosition].qty * (1 - [OrderPosition].discount)) as [total]
9: from Sales.Customers as [Customer]
10: inner join Sales.Orders as [Order]
11: on[Customer].custid = [Order].custid
12: inner join sales.OrderDetails as [OrderPosition]
13: on [Order].orderid = [OrderPosition].orderid
14: order by [Customer].custid, [Order].orderid
15: for xml auto
16: , root ('CustomerOrders')
17: , elements;
18: go
Das XML-Codierte Ergebnis kann man in diesem Sreenshot bewundern:
Jetzt noch auf „Datei –> XML_... Speichern unter...“ klicken und fertig ist der Export:
Wie man sehen kann, enthält diese Datei gültigen XML-Code, wodurch sie problemlos von
Internet-Explorer, Chrome und anderen Parsern interpretiert werden kann.
So weit so gut; der Nachteil dieser Art des XML-Exports ist offensichtlich: Es gibt keine Art der Automatisierung um das Ganze zeitgesteuert ausführen zu können. Daher fällt diese Möglichkeit für viele Unternehmen wieder aus der engeren Wahl.
Die zweite Variante ändert das Vorgehen: Hier wird das Statement einfach in einen Command-Shell-Aufruf des Programms BCP gekapselt und ein direkter Export des Resultset durchgeführt. Der Übersicht halber ändern wir den Zielnamen zu XMLExport2.xml.
1: bcp "select [Customer].custid as [custid], [Customer].companyname as [companyname], [Order].orderid as [orderid], [Order].orderdate as [orderdate]
2: from tsql2012.Sales.Customers as [Customer]
3: inner join tsql2012.Sales.Orders as [Order] on[Customer].custid = [Order].custid
4: order by [Customer].custid, [Order].orderid for xml auto, root ('CustomerOrders')" queryout "c:\users\administrator.training\desktop\XMLExport2.XML"
5: -w -T -S localhost -d TSQL2012
Wie man sieht, übergebe ich das ursprüngliche T-SQL-Statement als Ausführungsparameter an BCP.exe, mit den Parametern queryout und -w lege ich den Zielpfad bzw. die Zeichencodierung auf UniCode fest. Die Paramter -S, -d, -T geben den Zielserver, die Datenbank und den Trusted-State der Verbindung zum SQL-Server an.
Das Ergebnis - die Datei XMLExport2.xml stellt sich im Internet-Explorer wie folgt dar:
Dies lässt sich darauf zurückführen, dass BCP in jeder Zeile an der 2034. Stelle einen erzwungenen Zeilenumbruch einfügt und damit die XML-Codierung zerstört:
Dies führt in letzter Konsequenz dazu, dass Parser den Inhalt nicht mehr direkt lesen können. Um dies zu erreichen ist es also notwendig nachträglich eine Inhaltsanpassung durch zu führen, die in ihrer einfachsten Variante alle Zeilenumbrüche wieder entfernt.
Die dritte Variante versucht genau diese Schwäche zu umgehen, indem das Resultset mittels eines Cursors in mehrere Ergebniszeilen gesplittet wird welche dann in einer Zwischentabelle gespeichert werden um sie danach mittels BCP in eine XML-Datei zu verpacken:
1: declare @row xml
2: declare @cursorReturn as int
3: declare CustomerCursor cursor fast_forward for
4: select custid from Sales.Customers
5:
6: open CustomerCursor;
7:
8: fetch next from CustomerCursor into @cursorReturn
9: while @@FETCH_STATUS = 0
10: begin
11: set @row =(
12: select
13: [Customer].custid as [custid],
14: [Customer].companyname as [companyname],
15: [Order].orderid as [orderid],
16: [Order].orderdate as [orderdate],
17: [OrderPosition].unitprice as [unitprice],
18: [OrderPosition].qty as [quantity],
19: ([OrderPosition].unitprice * [OrderPosition].qty * (1 - [OrderPosition].discount)) as [total]
20: from Sales.Customers as [Customer]
21: inner join Sales.Orders as [Order]
22: on[Customer].custid = [Order].custid
23: inner join sales.OrderDetails as [OrderPosition]
24: on [Order].orderid = [OrderPosition].orderid
25: where [Customer].custid = @cursorReturn
26: order by [Customer].custid, [Order].orderid
27: for xml auto
28: , elements)
29: insert into dbo.XMLExport3 values (@row)
30: fetch next from CustomerCursor into @cursorReturn;
31: end
gefolgt von diesem Commandshell-Befehl:
1: bcp "select xmlcontent from dbo.XMLExport3"
2: queryout "c:\users\administrator.training\desktop\XMLExport3.XML"
3: -w -T -S localhost -d TSQL2012
Allerdings kann es auch hier zu Problemen kommen:
1. Sollte der XML-Inhalt derZeilen in Zwischentabelle länger als 2033 Zeichen sein, wird auch hier ein erzwungener Zeilenumbruch eingesetzt und
2. Es wird kein Root-Tag in der XML Datei erzeugt, was auch wieder dazu führt, dass Parser den Inhalt nicht sauber interpretieren können:
Auch Alternativen zum Erzeugen von Root-Tags führen nicht zum Erfolg:
1: bcp "select '<CustomerOrders>';select xmlcontent
2: from dbo.XMLExport3;select'</CustomerOrders>'"
3: queryout "c:\users\administrator.training\desktop\XMLExport3.XML"
4: -w -T -S localhost -d TSQL2012
oder
1: bcp "select '<CustomerOrders>'"
2: queryout "c:\users\administrator.training\desktop\XMLExport3.XML"
3: -w -T -S localhost -d TSQL2012
4:
5: bcp "select xmlcontent from dbo.XMLExport3"
6: queryout "c:\users\administrator.training\desktop\XMLExport3.XML"
7: -w -T -S localhost -d TSQL2012
8:
9: bcp "select'</CustomerOrders>'"
10: queryout "c:\users\administrator.training\desktop\XMLExport3.XML"
11: -w -T -S localhost -d TSQL2012
In beiden Fällen ist dies das Ergebnis:
Wie man sehen kann, sind die verschiedenen im Internet angebotenen Varianten eines XML-Exports mit individuellen Schwächen behaftet, die sie in Abhängigkeit von der Zielsetzung mehr oder minder interessant oder nutzbar machen.
Im nächsten Artikel werden wir betrachten , wie mit SQL-Server-2012-Funktionen und einem kleinen Workaround doch noch ein direkter XML-Export gelingen kann, der sogar per SQL-Agent automatisierbar ist.