Tabellen aus XML/XSD erzeugen

[Jasocul]

Bevor ich noch mehr sinnlose Zeit in Internet-Recherchen verbrate, dachte ich mir, vielleicht hatte jemand aus der DP auch schon das Problem.

Wir bekommen täglich mehrere 100 XML-Dateien mit sehr vielen Infos, wovon wir derzeit nur einen Bruchteil nutzen. Jetzt kam aber jemand auf die Idee, man könne doch alle Daten ins Datawarehouse einbauen. Prinzipiell ja kein schlechter Gedanke, aber er muss das ja nicht machen.

In den XML-Dateien sind seehr viele Knoten. Dazu auch noch einige als Listen (quasi 1:n-Beziehungen), die evtl. auch noch geschachtelt sein können.

Bei der Erst-Programmierung habe ich zwar entsprechende Klassen programmiert, damit schonmmal alle Daten gelesen werden können, damit auf Wunsch relativ einfach einzelne Informationen ins DWH hinzugefügt werden können. Um einzelne Felder in den DB-Tabellen nachzuflegen, habe ich dafür natürlich keine Routinen eingebaut.

Eine XSD-Datei habe ich derzeit nicht und ob ich eine vom Hersteller bekomme, weiß ich nicht. Ich vermute eher nicht. Also habe ich mir einen Generator gesucht, der aus einer XML-Datei eine XSD erzeugt (knapp 2000 Zeilen). Klar ist das dann nicht vollständig und die Typen sind nicht optimal, aber das wäre ja erstmal ein Anfang. Man könnte ja auch mehrere XML-Dateien durchlaufen lassen, damit die XSD möglichst vollständig wird.

Mit diesem Anfang würde ich gerne passende Tabellen in der Datenbank erzeugen. Natürlich gleich mit den gewünschten 1:n-Beziehungen. Das dann noch einiges an manuellen Anpassungen erforderlich sein wird, ist mir bewusst. Aber wenn schonmal die Tabellen mit den Felder überhaupt da sind, wäre mir schon sehr geholfen.

Fragen:
- Gibt es eine Möglichkeit, Tabellen auf diese Weise erzeugen zu lassen?
- Gibt es einen XSD-Generator, der das Ergebnis mit mehreren XML-Dateien erzeugt?
- Welche Probleme können sich ergeben?
- Weitere Tipps und Tricks?

Peter

[jobo]

100te Dateien a wieviel Varianten?

Ich weiß nicht, ob der Generator viel weiterhilft, wenn er eine Dynamik bringt, die ggf. am DB Ende nicht abgebildet werden kann. (also dynamische Alter Table DDL Statements.

Es gibt in MS SQL ähnlich wie in Oracle - woher ich es kenne- einige nette XML Funktionen.

In Oracle wäre meine Idee vielleicht folgende:
1. beliebig viele Dateien einer Sorte als eine(1) "external Table" deklarieren. (Das geht)
2. die external Definition über einen View gezielt ansprechen und "geglättet" zur Verfügung stellen (Das geht)
3. Daten über View einlesen oder einfach permanent im externen Zugriff belassen (Das geht)

1 bedeutet, für jede neue Datei eines Typs muss die external Definition angepasst werden (die zusätzliche Datei muss aufgeführt werden)
2 bedeutet, etwas Handarbeit (statt Generator), Datenentnahme auf Feldebene definieren, vielleicht nicht alles auf einmal, sondern nur die Rosinen
3 per definiertem View die Daten dem DWH bereitstellen, Import wäre dabei ein weiterer Schritt. Ein rein lesender Zugriff auf external XML wäre dann mit 3 fertig, Strukturänderungen (bei neuen Spalten) gäbe es nur in den Interface Views bzw. External Deklaration (und in DWH Teilen, die darauf zugreifen)

3 "ohne Import" hätte den netten Effekt, dass man alle Daten schon im Bauch hat und die View Definitionen nach Bedarf erweitern kann


also alles ohne XSLT und ohne XSLT Generator
und ohne Funktionsgarantie, nur eine Idee für MS SQL

(In Oracle habe ich das so ähnlich mal umgesetzt, nur mehrere Files gleichzeitig waren nicht gefordert. Es wird dateiweise importiert bzw. bereitgestellt.)

[hoika]

Hallo,
ich würde die Dateien einfach als Blob speichern.
Das Auslesen erfolgt dann mit den schon angesprochenen XML-Funktionen des Servers.

[Jasocul]

@hoika:
Ich werde mir die XML-Funktionen des Servers nochmal etwas genauer ansehen. Wenn die das verarbeiten können, wäre das eine einfache Lösung. Aber ich fürchte, dass das schwierig wird. Eine Datei ist ein Datensatz. Dieser enthält noch einige 1:n-Beziehungen. Ich werde den Ansatz aber mal genauer prüfen.

Zitat von jobo:

100te Dateien a wieviel Varianten?

Nur eine Variante. Allerdings sind Knoten nicht vorhanden, wenn es dafür keine Daten gibt.

Zitat von jobo:

Es gibt in MS SQL ähnlich wie in Oracle - woher ich es kenne- einige nette XML Funktionen.

Werde ich mir auf jeden Fall nochmal ganz genau ansehen.

Zitat von jobo:

1. beliebig viele Dateien einer Sorte als eine(1) "external Table" deklarieren. (Das geht)

Eine Datei ist ein Datensatz mit mehreren 1:n-Beziehungen in den Knoten, die weitere 1:n-Beziehungen haben können. Die Dateien als eine(1) Datei zu definieren, sollte lösbar sein. Bei den 1:n-Beziehungen könnte es schwierig werden. Habe ich aber noch nicht geprüft.

Zitat von jobo:

1 bedeutet, für jede neue Datei eines Typs muss die external Definition angepasst werden (die zusätzliche Datei muss aufgeführt werden)

Das würde bedeuten, dass mit jeder neuen Datenlieferung die Definition korrigiert werden muss. Wenn das automatisierbar ist, kann man das machen. Wir haben mindestens zwei Lieferungen am Tag mit insgesamt 200 bis 600 Dateien, schätze ich. Manuell wird das nicht umsetzbar sein. Oder man definiert eine neue Datei, in der alle anderen einfach reinkopiert werden.

Zitat von jobo:

2 bedeutet, etwas Handarbeit (statt Generator), Datenentnahme auf Feldebene definieren, vielleicht nicht alles auf einmal, sondern nur die Rosinen

Die Handarbeit wäre ja nur einmal. Updates sind da sehr selten. Aber wenn ich das sowieso definieren müsste, kann ich auch gleich eine Import-Funktion schreiben, die das direkt in die notwendigen Tabellen schreibt. Wäre ja auch nur eine einmalige Sache.

Zitat von jobo:

3 "ohne Import" hätte den netten Effekt, dass man alle Daten schon im Bauch hat und die View Definitionen nach Bedarf erweitern kann

Wenn die Performance dann auch ausreichend ist. Jeder der Dateien hat ca. 100 bis 150 kB. Ich habe da Befürchtungen, wenn die als External angesprochen werden.

Danke für eure Ideen. Ich werde mal prüfen, was davon machbar ist.

[jobo]

Hier ist ein einfaches Beispiel (s.u.)aus dem Netz wie es mit Oracle gehen kann. (ich hab das alles nicht mehr so präsent, ist schon ewig her bei uns- aber läuft noch) Die Location Definition ist dann die Stelle, wo mehrere Dateien angegeben würden und diese Stelle muss dann jeweils geändert werden. (Quasi ein Alter Table) Bin mir grad nicht ganz sicher, vielleicht gehen da auch schon Wildcards (ist aber auch egal, es geht ja im Detail um die MS SQL Funktionen).
Bei Oracle geht es jedenfalls, es geht auch Preprozessoren zu definieren, die die Dateien vor dem External Zugriff verketten. Das ist besonders easy bei CSV ohne Header, klar. Bei XML, gerade ordentlich aufgebauten, ist das nicht unbedingt selbstverständlich, weil es mit bloßem Append nicht getan ist. Mit zuvor verketteten Dateien ist die Definition dann aber statisch.
Wie genau die MSSQL SP das alles nachstellen, weiß ich natürlich nicht. Aber die Erfahrung ist, dass oft sehr ähnliche Dinge zur Verfügung stehen, einfach damit die nächste Success Story (Übernahme einer Oracle DB nach MS) leichter gelingt.

Das Prinzip ist meist wie in diesem unten Beispiel, das komplette XML gesamt als eine "Tabelle" zu haben. Mehrere Datensätze stünden dann für mehrere XML Blobs, die für sich natürlich auch beliebig viele Datensätze haben können. Bei einer statischen External Definition auf XML gibt es aber erstmal nur 1 Datensatz.

Zugriff / Transformation erfolgt erst durch das Select/View, was auf die External Tabelle aufsetzt.

Die Dateigröße würde ich als unkritisch ansehen. External Tables, jedenfalls diese, sind idR readonly, da fällt ein Haufen Overhead weg in der Bearbeitung.
1:n sollte auch kein Problem sein, erstmal wird XML sowieso immer als Block angesprochen, dadrin wird dann über Xpath o.ä. auch child Elemente verfügbar gemacht.
Performance erreicht man spätestens, wenn man wirklich im 2. Schritt importiert. Dann kann man indizieren usw.

"Einmaliger Aufwand" bei Handarbeit
Wie gesagt, den kann man initial schlank halten und nach und nach erweitern (Die Selects, die die Daten aus dem XML popeln)

Vielleicht kann man versuchen sowas wie im Link einfach als Proof nachzustellen..

https://oracle-base.com/articles/12c...ocuments-12cr2

[Jasocul]

Danke, Jo.
Ich werde diese Woche nicht mehr dazu kommen, da tiefer einzusteigen, werde in dem Link aber noch genauer nachlesen.