XML-Export leicht gemacht–Teil 2

Wie wir im vorigen Artikel gesehen haben, ist es nicht mit Bordmitteln problemlos möglich, einen sauberen Export relationaler Datasets in eine gültige XML-Syntax durchzuführen.
Der SQL-Server erlaubt es uns allerdings Binärobjekte direkt aus einem SQL-Statement zu erzeugen, die XML-Codierte Inhalte haben.

   1: use TSQL2012
   2: go
   3: declare @result xml
   4: set @result = (select 
   5:   [Customer].custid as [custid],
   6:   [Customer].companyname as [companyname],
   7:   [Order].orderid as [orderid],
   8:   [Order].orderdate as [orderdate],
   9:   [OrderPosition].unitprice as [unitprice],
  10:   [OrderPosition].qty as [quantity],
  11:   ([OrderPosition].unitprice * [OrderPosition].qty * (1 - [OrderPosition].discount)) as [total]
  12: from Sales.Customers as [Customer]
  13:   inner join Sales.Orders as [Order]
  14:   on[Customer].custid = [Order].custid
  15:   inner join sales.OrderDetails as [OrderPosition]
  16:   on [Order].orderid = [OrderPosition].orderid
  17: order by [Customer].custid, [Order].orderid
  18: for xml auto
  19: , root ('CustomerOrders')
  20: , elements);
  21: select cast(@result as varbinary(max))

Diese Binärobjekte könen dann als Zeileninhalt einer Spalte vom Typ varbinary(max) in der Datenbank gespeichert werden. Hierbei geht die keinerlei Infomation verloren und es werden auch keine zusätzliche Sonder- oder Steuerzeichen eingefügt.
An genau dieser Stelle können wir mit der SQL-Server Version 2012 einhaken und mittels einer neuen Filestreamfunktionalität einen kleinen, schmutzigen Workaround bauen.
Hierzu ist allerdings erst ein wenig administrative Arbeit am SQL-Server nötig.

Wir müssen:

1. Auf Dienstebene das Feature File-Streaming aktivieren

2. Der SQL-Server-Instanz beibringen, das dies zu nutzen ist und

3. Eine neue Datenbank mit Filestreaming-Repistory erzeugen oder einer bestehenden Datenbank ein Solches hinzufügen

Für die Schritte 1 und 2 sind sysadmin-Rechte vonnöten, während Schritt mit dbcreator- bzw. db_owner-Rechten ausgeführt werden kann.

Schritt 1 ist noch am wenigsten aufwendig: hierzu öffnen wir einfach den
SQL-Server-Konfigurationsmanager, wählen im Knoten SQL-Server-Dienste den SQL-Server-Eintrag unserer Instanz und aktivieren auf der Registerkarte FileStream die drei Checkboxen.
Im zuge dessen wird eine Windows-Dateifreigabe erzeugt, die den Namen der SQL-Server-Instanz trägt und als Schnittstelle zwischen dem SMB-Protokoll für die Datenübertragung über das Netzwerk und dem SQL-Server selbst fungiert. Nur über diesen Weg können wir später auf unsere erstellten XML-Dateien zugreifen.
fs1
fs2
fs3

Für die Schritte 2 und 3 verwenden wir das SQL-Server-Management-Studio. Der 2. Schritt besteht darin, in der Instanzkonfiguration die Verwendung von FileStream zu ermöglichen. Auch hier ist der Ablauf ziemlich simpel: Rechtsklick auf den Instanznamen à Eigenschaften und dann auf der Seite „Erweitert“ im Knoten „FileStream“ den Wert für „FILESTREAM-Zugriffsebene“ auf „Vollzugriff aktiviert“ setzen.
fs4
fs5

Sollte das Filestream-Feature bereits während des SQL-Server-Setups aktiviert worden sein, sind die Schritte 1 und 2 unnötig.

Schritt 3 besteht aus 2 Alternativen: a) wir erstellen eine neue Datenbank oder b) wir fügen einer bestehenden Datenbank ein Filestream Repository hinzu.
Zu Demonstrationzwecken verwenden wir in beiden Fällen die Datenbank FSDemo.
Varinte a) funktioniert über den Datenbank-Erstellen-Dialog in dem wir (in dieser Reihenfolge) erst eine Filestream-Dateigruppe erstellen und danach eine Datebankdatei zu dieser Dateigruppe hinzufügen. Hierzu verwenden wir die Seite Dateigruppen und erstellen im Bereich FileStream eine neue Dateigruppe mit dem Namen fgFileStream. Als nächstes erzeugen wir auf der Seite Allgemein eine weitere Datei namens fsRepository, ändern ihren Typ auf Filestream und sehen, das die Dateigruppenzuordung autmatisch auf fgFileStream gesetzt wird.
fs6
fs7
fs8

Nun muss in den Eigenschaften der neuen Datenbank die Nutzung von FileStream konfiguriert werden. Auf der Seite Optionen setzt man im Knoten Filesteam die Werte für
„Filestream-Verzeichnisname“ und „Nicht transaktionsgebundener Filesteam-Zugriff“ auf den Datenbanknamen bzw. „Full“.
fs9

Natürlich kann auch hier ein TSQL-Statement als Alternative eingesetzt werden:

   1: USE [master]
   2: GO
   3: CREATE DATABASE [FSDemo]
   4:  CONTAINMENT = NONE
   5:  ON  PRIMARY 
   6: ( NAME = N'FSDemo', FILENAME = N'C:\_SQLDB\MSSQL13.MSSQLSERVER\MSSQL\DATA\FSDemo.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ), 
   7:  FILEGROUP [fgFileStream] CONTAINS FILESTREAM 
   8: ( NAME = N'fsRepository', FILENAME = N'C:\_SQLDB\MSSQL13.MSSQLSERVER\MSSQL\DATA\fsRepository' )
   9:  LOG ON 
  10: ( NAME = N'FSDemo_log', FILENAME = N'C:\_SQLDB\MSSQL13.MSSQLSERVER\MSSQL\DATA\FSDemo_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
  11: GO
  12: ALTER DATABASE [FSDemo] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FSDemo' ) WITH NO_WAIT
  13: GO
  14: USE [FSDemo]
  15: GO
  16: IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [FSDemo] MODIFY FILEGROUP [PRIMARY] DEFAULT
  17: GO

Die zweite Möglichkeit, Variante b) verwendet den Eigenschaften-Dialog der Datenbank FSDemo um die Filestream-Dateigruppe und das Filestream-Repository zu erzeugen. Die Reihenfolge der Abzuarbeitenden Schritte ist identisch mit Variante a). Zuerst erstellen wir auf der Seite Dateigruppen im Bereich FileStream eine neue Dateigruppe mit dem Namen fgFileStream. Danach erzeugen wir auf der Seite Dateien eine weitere Datei namens fsRepository, ändern ihren Typ auf Filestream und sehen, das die Dateigruppenzuordung autmatisch auf fgFileStream gesetzt wird.
fs10
fs11

Zuletzt wird auch hier die Nutzung von FileStream konfiguriert.
Auf der Seite Optionen setzt man im Knoten Filestream die Werte für „Filestream-Verzeichnisname“ und „Nicht transaktionsgebundener Filesteam-Zugriff“ auf den Namen der Datenbank bzw. „Full“.
fs12

Analog zu Variante a) kann stattdessen auch ein TSQL-Statement zum Einsatz kommen:

   1: USE [master]
   2: GO
   3: ALTER DATABASE [FSDemo] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FSDemo' ) WITH NO_WAIT
   4: GO
   5: ALTER DATABASE [FSDemo] ADD FILEGROUP [fgFileStream] CONTAINS FILESTREAM 
   6: GO
   7: ALTER DATABASE [FSDemo] ADD FILE ( NAME = N'fsRepository', FILENAME = N'C:\_SQLDB\MSSQL13.MSSQLSERVER\MSSQL\DATA\fsRepository' ) TO FILEGROUP [fgFileStream]
   8: GO

Wir können nun im Standardpfad für Datenbankdateien ein Verzeichnis mit dem Namen „fsRepository“ sehen. Hier werden de Binärobjekte, die in FileStream-Spalten einer oder mehrerer Tabellen geschrieben werden, abgelegt.
Das Speicherziel, in das unsere XML-Dateien geschrieben werden sollen, erzeugen wir jetzt in der Datenbank FSDemo. Dieses Speicherziel wird als sogenannte Dateitabelle erzeugt, eine FilestreamFunktion, die erst seit der SQL-Server-Version 2012 verfügbar ist und es erlaubt, mittels der Filestream-Freigabe, die wir zu Beginn dieses Artikels erzeugten, auf den Inhalt des Filesteam-Repositorys zuzugreifen.

Die Dateitabelle, die wir erzeugen, wird ExportLibrary heissen und kann nur mittels eines TSQL-Statements generiert werden:

   1: USE FSDemo
   2: GO
   3: CREATE TABLE ExportLibrary AS FILETABLE
   4:   WITH
   5:   (
   6:     FILETABLE_DIRECTORY = 'ExportLibrary',
   7:     FILETABLE_COLLATE_FILENAME = database_default
   8:   )
   9: GO

Die Optionen, die zusätzlich konfiguriert werden, erzeugen innerhalb der Filstream-Freigabe einen Unterordner für die Dateitabelle mit dem angegebenen Namen und setzen die Tabellen spezifische Sortierung auf den Datenbank-Standard fest.
Die aus unserer vorbereitenden Arbeit restultierenden Ordnerstrukturen werden nachfolgen dargestellt:
fs13

Links sehen wir die Struktur der FileStream-Freigabe und rechts das physische Filestream-Repository, zu beachten sind hier die beiden Verzeichnisse, die GUIDs als Namen tragen: dies sind, von oben nach unten ,die ObjectID der Tabelle bzw. der Binärspalte „file_stream“ innerhalb der Tabelle.
Für unsere Zwecke ist das physische Filestream-Repository vollkommen uninteressant, weshalb dieses keine weitere Erwähnung finden wird.

Nachdem wir jetzt ein Speicherziel erzeugt haben, das es uns erlaubt Binärdateien direkt aus einem TSQL-Statement zu generieren, können wir mit folgendem Statement eine Datei namens XMLExport4.xml in der Tabelle FSDemo.dbo.ExportLibrary erstellen und sie anschließend über die Filestream-Freigabe auf den Desktop kopieren und anzeigen lassen:

   1: use TSQL2012
   2: go
   3: declare @result xml
   4: set @result = (select 
   5:   [Customer].custid as [custid],
   6:   [Customer].companyname as [companyname],
   7:   [Order].orderid as [orderid],
   8:   [Order].orderdate as [orderdate],
   9:   [OrderPosition].unitprice as [unitprice],
  10:   [OrderPosition].qty as [quantity],
  11:   ([OrderPosition].unitprice * [OrderPosition].qty * (1 - [OrderPosition].discount)) as [total]
  12: from Sales.Customers as [Customer]
  13:   inner join Sales.Orders as [Order]
  14:   on[Customer].custid = [Order].custid
  15:   inner join sales.OrderDetails as [OrderPosition]
  16:   on [Order].orderid = [OrderPosition].orderid
  17: order by [Customer].custid, [Order].orderid
  18: for xml auto
  19: , root ('CustomerOrders')
  20: , elements);
  21: insert into FSDemo.dbo.ExportLibrary (file_stream, name) values (cast(@result as varbinary(max)), 'XMLExport4.xml')
  22: go

Das Ergebnis ist eine vollständige, gültige XML-Datei, die sogar in einem Standardpfad abgelegt wird und von automatisiert ausgeführten Parsern immer unter diesem Pfad erreicht werden kann.
fs14

Die Ergebnis-Datei wird allerdings ohne irgenwelche XML-Formatinformationen, sondern als eine fortlaufende Zeile binär gespeichert. Wenn auf Lesbarkeit ausserhalb interpretierender Programme wie Internet-Explorer, Word, etc. Wert gelegt wird, muss eine nachträgliche Formatierung durchgeführt werden.

Wir sehen also, dass mit dem SQL-Server 2012 die Möglichkeit zum direkten Export relationaler Datasets in XML-Dateien mitgleifert wird. Diese Variante ist allerding recht gut versteckt und erfordert Eingriffe in die Konfiguration der SQL-Server-Instanz sowie der Datenbank. Dieser Mehraufwand wird allerings durch die Vorteile dieser Vorgehensweise wie vollständige Automatisierbarkeit oder feste Zielpfade für den Export mehr als aufgewogen.
Hierdurch wird die Integration des MS-SQL-Server mit anderen Systemen vereinfacht und dieses Vorgehen kann auch verwendet werden, um die mit SQL 2016 eingeführte native Unterstützung für JSON zu verwenden und direkte Exporte in JSON-Dateien durch zu führen.

Kommentare sind geschlossen