Große Menge XML-Dateien in MSSQL-DB einlesen - Frage zur Schemadatei

[Dimo Tabken]

Moin in die Runde,
ich bin "der Neue hier" und habe Euer Forum bei meinem Versuch gefunden, eine funktionierende Schema-Datei für den Import von ca. 1,5 Mio XML-Dateien in eine SQL-Datenbank zu erzeugen.
Ich bin, was XML angeht, ein ziemlicher Neuling, speziell mit der Frage zu Schemas habe ich mich bisher nicht auseinandergesetzt, das wird sich nun ändern müssen.
Zu meiner konkreten Herausforderung:

Ich habe XML-Dateien, die alle den folgenden Aufbau haben:

<?xml version="1.0" encoding="utf-8"?>
<Document ID="0000a\00d48436-7edc-45c1-ac56-8fe28bfd1f17">
  <Fields>
    <DocumentId Name="DocumentId" Type="System.String" Mandatory="false"><![CDATA[437445]]></DocumentId>
  </Fields>
  <Files>
    <File ID="0001" Name="IrgendEinDocName.doc" Size="55296" MimeType="application/octet-stream" IsEncrypted="false" />
  </Files>
</Document>

Bei meiner Recherche habe ich das Vorgehen gefunden, parallel zu der XML-Datei noch eine Schemadatei zu erzeugen und die dann mittels SQLXMLBULKLOAD aus einem VBScript in die Datenbank zu importieren.
DAS funktioniert rein technisch und die VB-Routine, aus sämtlichen Unterordnern in meiner Dateistruktur die XML-Dateien auszulesen und abzuarbeiten, das ist auch kein Problem.
Nur mit der Schemadatei tu ich mich schwer.

Momentan sieht sie wie folgt aus:

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

   <ElementType name="Document" sql:is-constant="1">
      <element type="Fields" />
      <element type="Files" />
   </ElementType>

   <ElementType name="Fields"  sql:relation="DocMeta">
    <AttributeType name="DocumentID" dt:type="int" />
      <attribute type="DocumentID"  sql:field="DocumentID" />
   </ElementType>
       <ElementType name="Files"  sql:relation="DocMeta">
           <AttributeType name="ID" dt:type="string" />
           <AttributeType name="Name" dt:type="string" />
           <AttributeType name="Size" dt:type="string" />
           <AttributeType name="MimeType" dt:type="string" />
            <attribute type="ID"  sql:field="FileID" />
            <attribute type="Name"  sql:field="DocName" />
            <attribute type="Size"  sql:field="DocSize" />
            <attribute type="MimeType"  sql:field="MimeType" />

   </ElementType>

</Schema>

Wenn ich das Script nun mit einer Beispieldatei laufen lasse, erzeugt es mir in meiner Datenbank zwei leere Datensätze, die Felder haben jeweils den Wert NULL.
Meine Tabelle DocMeta hat die fünf Spalten

DocumentID,
FileID,
DocName,
DocSize,
MimeType


Hat von Euch jemand einen Tipp für mich, wie ich die Schemadatei ändern muss, damit die Inhalte aus dem XML in die Datenbank übertragen werden?

Ich danke Euch schon jetzt ganz herzlich und wünsche ein feines Wochenende!

Viele Grüße
Dimo

[Christian Marquardt]

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
  <xs:element name="Document">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="Fields"/>
        <xs:element ref="Files"/>
      </xs:sequence>
      <xs:attribute name="ID" type="xs:string" use="required"/>
    </xs:complexType>
  </xs:element>
  <xs:element name="Fields">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="DocumentId"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="DocumentId">
    <xs:complexType>
      <xs:attribute name="Mandatory" type="xs:string" use="required"/>
      <xs:attribute name="Name" type="xs:string" use="required"/>
      <xs:attribute name="Type" type="xs:string" use="required"/>
    </xs:complexType>
  </xs:element>
  <xs:element name="Files">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="File"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="File">
    <xs:complexType>
      <xs:attribute name="ID" type="xs:string" use="required"/>
      <xs:attribute name="IsEncrypted" type="xs:string" use="required"/>
      <xs:attribute name="MimeType" type="xs:string" use="required"/>
      <xs:attribute name="Name" type="xs:string" use="required"/>
      <xs:attribute name="Size" type="xs:string" use="required"/>
    </xs:complexType>
  </xs:element>
</xs:schema>

[Dimo Tabken]

Hi Christian!
Dank Dir für Deine Antwort!
Wenn ich dem SQLXMLBulkload bzw. dem vbscript Dein Schema mitgebe, bekomme ich die folgende Fehlermeldung:

Validation failed on meta.xml
=======
Reason: Gemäß dem DTD/Schema ist im Zusammenhang mit Element 'DocumentId' kein Text zugelassen.

Source:     <DocumentId Name="DocumentId" Type="System.String" Mandatory="false"><![CDATA[437445]]></DocumentId>

Habe jetzt nach etwas Suche etwas gefunden, wo ich mir aus meiner XML-Beispieldatei ein Schema erstellen lassen konnte. Das Schema wird auch bei der Validation akzeptiert,
nun stehe ich aber vor der Herausforderung, die Verbindung zu meiner Datenbank herzustellen.
Gelernt habe ich bisher, dass ich den xmlns sql mit einbinden muss, damit das überhaupt funktioniert.

Was ich nachträglich eingefügt habe (Habe versucht, andere Beispiele gedanklich zu übertragen), markiere ich hier mal rot:

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql = "urn:schemas-microsoft-com:mapping-schema">
  <xs:element name="DocumentId">
    <xs:complexType>
      <xs:simpleContent>
        <xs:extension base="xs:int">
          <xs:attribute type="xs:string" name="Name"/>
          <xs:attribute type="xs:string" name="Type"/>
          <xs:attribute type="xs:string" name="Mandatory"/>
        </xs:extension>
      </xs:simpleContent>
    </xs:complexType>
  </xs:element>
  <xs:element name="File">
    <xs:complexType>
      <xs:simpleContent>
        <xs:extension base="xs:string">
          <xs:attribute type="xs:byte" name="ID"/>
          <xs:attribute type="xs:string" name="Name" sql:field="DocName"/>
          <xs:attribute type="xs:int" name="Size" sql:field="DocSize"/>
          <xs:attribute type="xs:string" name="MimeType" sql:field="MimeType"/>
          <xs:attribute type="xs:string" name="IsEncrypted"/>
        </xs:extension>
      </xs:simpleContent>
    </xs:complexType>
  </xs:element>
  <xs:element name="Fields" sql:relation="DocMeta">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="DocumentId"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="Files" sql:relation="DocMeta">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="File"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="Document">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="Fields"/>
        <xs:element ref="Files"/>
      </xs:sequence>
      <xs:attribute type="xs:string" name="ID" sql:field="DocumentID"/>
    </xs:complexType>
  </xs:element>
</xs:schema>

Das ist aber offensichtlich immer noch ziemlich falsch, denn ich bekomme nun die Meldung

meta1.vbs(17, 3) Schema mapping: Schema: relationship expected on 'Fields'

Und nun bin ich mit meinem Latein endgültig am Ende.
Hättest Du vielleicht nochmal einen Tipp für mich? Ich würde die Logik gerne verstehen, damit ich zukünftig nicht mehr ganz so doof dastehe ...

Vielen Dank!

[Martin Honnen]

Moin in die Runde,
ich bin "der Neue hier" und habe Euer Forum bei meinem Versuch gefunden, eine funktionierende Schema-Datei für den Import von ca. 1,5 Mio XML-Dateien in eine SQL-Datenbank zu erzeugen.
Ich bin, was XML angeht, ein ziemlicher Neuling, speziell mit der Frage zu Schemas habe ich mich bisher nicht auseinandergesetzt, das wird sich nun ändern müssen.
Zu meiner konkreten Herausforderung:

Ich habe XML-Dateien, die alle den folgenden Aufbau haben:

<?xml version="1.0" encoding="utf-8"?>
<Document ID="0000a\00d48436-7edc-45c1-ac56-8fe28bfd1f17">
<Fields>
<DocumentId Name="DocumentId" Type="System.String" Mandatory="false"><![CDATA[437445]]></DocumentId>
</Fields>
<Files>
<File ID="0001" Name="IrgendEinDocName.doc" Size="55296" MimeType="application/octet-stream" IsEncrypted="false" />
</Files>
</Document>

Wenn ich das Script nun mit einer Beispieldatei laufen lasse, erzeugt es mir in meiner Datenbank zwei leere Datensätze, die Felder haben jeweils den Wert NULL.
Meine Tabelle DocMeta hat die fünf Spalten

DocumentID,
FileID,
DocName,
DocSize,
MimeType

Hat jedes XML-Dokument nur ein "Document"-Element? Wird jedes "Document"-Element genau in eine Zeile der SQL Tabelle eingetragen? Bulkupload dient ja in erster Linie dazu, aus einem XML-Dokument mit Tausenden oder Millionen von Daten diese in entsprechende Zeilen der SQL-Tabelle einzulesen, aber doch nicht, um 1,5 Millionen getrennte Dateien zu verarbeiten.

Kannst du mal für das Beispiel angeben, welche Werte aus dem XML-Dokument in welche Spalte der Tabelle gehören? Werden manche Werte aus dem XML ignoriert bzw. nicht benötigt?

[Dimo Tabken]

Hallo Martin!
Ja, jedes XML-Dokument hat immer nur ein "Document"-Element.
Jedes XML-Dokument gehört in eine Zeile der SQL-DB.
Die 1,5 Mio. Dateien in die Datenbank zu überführen, ist leider gerade exakt meine Aufgabenstellung :-) Daher bin ich gerade am Wühlen, wie ich das hinbekomme.

Zu Deiner Frage nach einem Beispiel:

Die folgenden Zuordnungen brauche ich:

Datenbank	SQL-Element
DocumentID	Der DocID-Wert (437445) aus <DocumentID>
FileID	Attribut ID="0001" von <File>
DocName	Attribut Name="IrgendEinDocName.doc" von <File>
DocSize	Attribut Size="55296" von <File>
MimeType	Attribut MimeType="application/octet-stream" von <File>

Weitere Werte brauche ich für die Übernahme nicht. Was sonst noch an Werten in der XML steht, kann ich ignorieren.

Macht es das für Dich nachvollziehbarer?

[Christian Marquardt]

Warum muss das kompliziert über XML laufen?
XML-Datei öffnen
XML parsen
insert Statement mit den 5 Feldern
Nächste Datei

[Dimo Tabken]

OK, neuer Gedankenansatz, den ich noch nicht kenne, Christian. Ich hatte den Eindruck gewonnen, dass das über den ersten Weg der erfolgversprechendste Ansatz wäre.

hast Du eine Idee, welcher der Beiden Wege der Performantere sein würde? Bei *dieser* Menge XML-Dateien wird ja auch das eine Rolle spielen ...

[Christian Marquardt]

Nein, aber solange das einmaliger Vorgang ist, dauert es halt solange wie es dauert.
Und es wäre an der Zeit das mit einer richtigen Programmiersprache zu tun. VBScript ist eine tote Sprache.

[Dimo Tabken]

Ja, das ist absolut ein Argument, da gebe ich Dir recht.
Plan für die Zukunft ist das sicherlich, aber jetzt muss ich's halt irgendwie hinbiegen und mir fehlt die Zeit, mich erst in eine richtige Programmiersprache einzuarbeiten. Muss also zwangsläufig mit dem Arbeiten, was ich kann und habe :-(

[Christian Marquardt]

Dann bleib bei deinem Weg mit XML

[Dimo Tabken]

OK ... Hättest Du Zeit und Lust, in #3 nochmal reinzuschauen und hast evtl. einen Tipp für mich, was ich falsch mache?
Das wäre wirklich klasse.

[Christian Marquardt]

Nein, sorry, da
- gleich Zeit für Heia ist
- ich keine Lust habe mich in VBScript einzulesen
- ich nicht nachvollziehen kann, warum eine DB-Verbindung mit XML hergestellt wird

Vielleicht hat Martin da weitere Infos

[Dimo Tabken]

Alles klar, dank Dir für Deine Zeit, Christian!

[Martin Honnen]

Ich bin mir nicht sicher, ob SQLXMLBulkLoad das richtige Tool ist, insbesondere weil die Daten für eine Tabellenzeile in zwei verschiedenen Unterunterelementen "DocumentID" und "File" sitzen. Ich denke, mit https://docs.microsoft.com/en-us/sql...l-server-ver15 wird man das laden können, aber bei SQLXMLBulkLoad bin ich mir nicht sicher, ob das geht. Ansonsten, aus einer Programmiersprache heraus, sollte man bei solch kleinen XML-Dateien auch keine Probleme haben, per DOM mit MSXML oder XmlDocument in .NET oder XML LINQ eine Baumstruktur eines einzelnen Dokumentes einzulesen, dann per XPath oder LINQ die entsprechenden Werte auszulesen und dann per SQL Server API von der Sprache aus in eine Datenbank zu schreiben. SQLXMLBulkLoad braucht man ja eher, um XML-Daten einzulesen, bei der auf Grund der Größe gerade keine DOM oder andere Baumstruktur geladen werden kann.

[Martin Honnen]

Mit OPENXML ginge etwa

INSERT INTO DBName.dbo.DocMeta
SELECT *  
FROM   OPENXML (@idoc, '/Document')   
         WITH (DocumentID       int         'Fields/DocumentId',
               FileID  int 'Files/File/@ID',
               DocName   nvarchar(100)    'Files/File/@Name',
               DocSize      int         'Files/File/@Size',
               MimeType nvarchar(100)         'Files/File/@MimeType');