16
Import von XML-File in MS SQL-Server
Hallo,
ich habe die folgende XML-Datei und möchte sie in eine bestehende Tabelle auf unseren SQL-Server importieren:
Bisher habe ich mit dieser Form des Datentransfers keine Berührung gehabt und dachte immer XML wäre ein einfacher Standard, aber scheinbar ist das nicht so
Vielleicht sehe ich auch einfach den Wald vor lauter Bäumen nicht.
Das folgende Statement habe ich versucht:
Gibt es ein T-SQL-Statement mit dem ich diese Form von XML importieren kann?
Vielen Dank
ich habe die folgende XML-Datei und möchte sie in eine bestehende Tabelle auf unseren SQL-Server importieren:
<xml><report id="12ABCDE3-1234-ABCD-1234-1234ABDC1234" description="Report" execution="2021-06-10 10:20:53.750">
<table name="Fehlermde" rows="2">
<header>
<column no="1"><id>OrdName</id><label>OrdName</label><type>STRING</type></column>
<column no="2"><id>BestNr</id><label>BestNr</label><type>STRING</type></column>
<column no="3"><id>Objekt</id><label>Objekt</label><type>STRING</type></column>
<column no="4"><id>Datum</id><label>Datum</label><type>DATE</type></column>
</header>
<data>
<row>
<column no="1"><value>Kunde 1</value></column>
<column no="2"><value>2020-08-2291</value></column>
<column no="3"><value>Motorblock</value></column>
<column no="4"><value>2020-01-23</value></column>
</row>
<row>
<column no="1"><value>Kunde 2</value></column>
<column no="2"><value>2020-10-2431</value></column>
<column no="3"><value>Ventil</value></column>
<column no="4"><value>2020-02-11</value></column>
</row>
</data>
</table>
</report></xml>Bisher habe ich mit dieser Form des Datentransfers keine Berührung gehabt und dachte immer XML wäre ein einfacher Standard, aber scheinbar ist das nicht so
Vielleicht sehe ich auch einfach den Wald vor lauter Bäumen nicht.
Das folgende Statement habe ich versucht:
INSERT INTO tsc_Tab1 ([OrdName], [BestNr], [Objekt], [Datum])
SELECT
MY_XML.tscCustomer.query('1').value('.', 'VARCHAR(50)'),
MY_XML.tscCustomer.query('2').value('.', 'VARCHAR(50)'),
MY_XML.tscCustomer.query('3').value('.', 'VARCHAR(50)'),
MY_XML.tscCustomer.query('4').value('.', 'Date')
FROM (SELECT CAST(MY_XML AS xml)
FROM OPENROWSET(BULK 'd:\temp\test1.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
CROSS APPLY MY_XML.nodes('data/row') AS MY_XML (tscCustomer); Gibt es ein T-SQL-Statement mit dem ich diese Form von XML importieren kann?
Vielen Dank
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 1186739414
Url: https://administrator.de/contentid/1186739414
Ausgedruckt am: 22.11.2024 um 06:11 Uhr
16 Kommentare
Neuester Kommentar
nimm den Datenimport/Export Assistenten, der unterstützt auch XML.
Und ich meine irgendwo konnte man am Ende auch ein SQL Statement abspeichern.
Und ich meine irgendwo konnte man am Ende auch ein SQL Statement abspeichern.
Hallo, interessant wäre mal die Fehlermeldung
Zitat von @onkel-ossi:
Bisher habe ich mit dieser Form des Datentransfers keine Berührung gehabt und dachte immer XML wäre ein einfacher Standard, aber scheinbar ist das nicht so
Ja ist ein Standard, einfach ist Geschmackssache. Was genau geht denn nicht? Liefert der Select sinnvolle Daten wenn im SSMS ausgeführt? Welche Fehlermeldung?Bisher habe ich mit dieser Form des Datentransfers keine Berührung gehabt und dachte immer XML wäre ein einfacher Standard, aber scheinbar ist das nicht so
Es kommt keine Fehlermeldung, sondern dass o Datensätze betrofen sind:
Aktuell versuche ich es im SQL Server Management Studio (SSMS)
(0 row(s) affected)Aktuell versuche ich es im SQL Server Management Studio (SSMS)
So wie ich das sehe hast du ein Problem wie du die "Spalten" innerhalb der xml abfrägst. Genauer gesagt die Information die du haben willst sind nicht vorhanden.
Hab da selbst wenig Erfahrung aber schau dir mal anstelle von query() nodes() an.
https://docs.microsoft.com/de-de/sql/t-sql/xml/nodes-method-xml-data-typ ...
Hab da selbst wenig Erfahrung aber schau dir mal anstelle von query() nodes() an.
https://docs.microsoft.com/de-de/sql/t-sql/xml/nodes-method-xml-data-typ ...
Es kann auch sein das BULK Insert und CROSS APPLY sich nicht vertragen, ganz ganz dunkel erinnere ich mich das mal versucht zu haben und irgendeine damals einleuchtende Erklärung gefunden zu haben. Kannst du mal versuchen ob der XML Code funktioniert wenn du die XML Spalte aus einer Tabelle holst?
Hallo ukulele-7,
wenn du meinst, ob das Zwischenobjekt eine Tabelle mit XML-Feld liefert - ja, das funktioniert:
liefert mir eine einspaltige Tabelle mit der XML-Datei als Inhalt.
wenn du meinst, ob das Zwischenobjekt eine Tabelle mit XML-Feld liefert - ja, das funktioniert:
(SELECT CAST(MY_XML AS xml)
FROM OPENROWSET(BULK 'd:\temp\test1.xml', SINGLE_BLOB) AS T(MY_XML)) liefert mir eine einspaltige Tabelle mit der XML-Datei als Inhalt.
Aber wenn ich das Insert weg lasse und nur ein Select habe liefert er keine Daten bzw. Leere Spalten
Hallo wiesi200,
mit der Seite habe ich heute früh schon ein wenig mehr erreicht!
Liefert mir schon einmal zwei Datensätze:
mit der Seite habe ich heute früh schon ein wenig mehr erreicht!
SELECT
tscCustomer.query('column/value') as result
FROM (SELECT CAST(MY2_XML AS xml)
FROM OPENROWSET(BULK 'd:\temp\test2.xml', SINGLE_BLOB) AS T(MY2_XML)) AS T(MY1_XML)
CROSS APPLY MY1_XML.nodes('/xml/report/table/data/row') AS MY3_XML(tscCustomer); Liefert mir schon einmal zwei Datensätze:
<value>Kunde 1</value><value>2020-08-2291</value><value>Motorblock</value><value>2020-01-23</value>
<value>Kunde 2</value><value>2020-10-2431</value><value>Ventil</value><value>2020-02-11</value>
Nein ich war mir nicht sicher ob sich die Ausgabe von BULK Insert direkt mit XML Funktionen auslesen läßt oder ob man erst einen Zwischenschritt braucht. Scheint ja aber zu gehen.
Dein "Problem" bei deiner XML ist das die Spaltenbezeichnung etwas ungünstig ist.
So, nach vielen Versuchen habe ich es jetzt hinbekommen!
Aus der folgenden XML-Datei:
macht dieses T-SQL-Statement:
die folgende Struktur:
Wichtige Erkenntnisse:
In dem Sinne: Danke an alle die geholfen haben!
Gruß
Thomas
Aus der folgenden XML-Datei:
<xml>
<report id="12ABCDE3-1234-ABCD-1234-1234ABDC1234" description="Report" execution="2021-06-10 10:20:53.750">
<table name="Fehlermde" rows="2">
<header>
<column no="1"><id>OrdName</id><label>OrdName</label><type>STRING</type></column>
<column no="2"><id>BestNr</id><label>BestNr</label><type>STRING</type></column>
<column no="3"><id>Objekt</id><label>Objekt</label><type>STRING</type></column>
<column no="4"><id>Datum</id><label>Datum</label><type>DATE</type></column>
</header>
<data>
<row>
<column no="1"><value>Kunde 1</value></column>
<column no="2"><value>2020-08-2291</value></column>
<column no="3"><value>Motorblock</value></column>
<column no="4"><value>2020-01-23</value></column>
</row>
<row>
<column no="1"><value>Kunde 2</value></column>
<column no="2"><value>2020-10-2431</value></column>
<column no="3"><value>Ventil</value></column>
<column no="4"><value>2020-02-11</value></column>
</row>
</data>
</table>
</report>
</xml>macht dieses T-SQL-Statement:
SELECT
tsc1Customer.value('(column)[1]', 'CHAR(50)') as Spalte1,
tsc1Customer.value('(column)[2]', 'CHAR(50)') as Spalte2,
tsc1Customer.value('(column)[3]', 'CHAR(50)') as Spalte3,
tsc1Customer.value('(column)[4]', 'CHAR(50)') as Spalte4
FROM
(
SELECT
tscCustomer.query('.') as result
FROM
(
SELECT
CAST(MY2_XML AS xml)
FROM
OPENROWSET(BULK 'd:\temp\test2.xml', SINGLE_BLOB)
AS T(MY2_XML)
)
AS T(MY1_XML)
CROSS APPLY MY1_XML.nodes('xml/report/table/data/row') AS MY3_XML(tscCustomer)
)
AS T(MY01_XML)
CROSS APPLY MY01_XML.nodes('row') AS MY03_XML(tsc1Customer) die folgende Struktur:
Kunde 1 2020-08-2291 Motorblock 2020-01-23
Kunde 2 2020-10-2431 Ventil 2020-02-11 Wichtige Erkenntnisse:
- Standard bedeutet nicht einfach ...
- XML ist cASe-Sensitiv - "ROW" <> "row"
- Die Umwandlung auf diesem Weg ist langsam (700 Zeilen in ca. 1:50 min)
- ich liebe INI-Dateien
In dem Sinne: Danke an alle die geholfen haben!
Gruß
Thomas
Die Umwandlungsgeschwindigkeit ist aber definitiv abhängig von BULK Insert. Du kannst auch eine Tabelle in SQL anlegen mit einer Spalte Datentyp XML, dort importierten und dann den Select darauf machen, das läuft mit Sicherheit schneller. Bei nur einer XML die importiert werden soll ist das aber zu vernachlässigen.
Ich habe hier eine wirklich seltsame XML-Definition.
Insgesamt gibt es in der Datei vier Table-Bereiche mit unterschiedlichen Datensätzen. In Summe ware es knapp 700 Datensätze.
Für das einlesen braucht der SQL-Server die genannten knapp 2 Minuten und auch noch nicht ganz so wie ich es benötigte.
Mittlerweile kann ich jetzt jede Tabelle einzeln auswerten, die Zeit liegt jetzt unter 5 Sekunden pro Tabelle und damit im akzeptablen Rahmen.
Ziel ist also erreicht: Gesamte XML jetzt in vier Tabellen aufsplitten und die Daten in SQL nutzen
Insgesamt gibt es in der Datei vier Table-Bereiche mit unterschiedlichen Datensätzen. In Summe ware es knapp 700 Datensätze.
Für das einlesen braucht der SQL-Server die genannten knapp 2 Minuten und auch noch nicht ganz so wie ich es benötigte.
<xml>
<report id="12ABCDE3-1234-ABCD-1234-1234ABDC1234" description="Report" execution="2021-06-10 10:20:53.750">
<table name="Fehlermde" rows="2">
<header>
<column no="1"><id>OrdName</id><label>OrdName</label><type>STRING</type></column>
<column no="2"><id>BestNr</id><label>BestNr</label><type>STRING</type></column>
<column no="3"><id>Objekt</id><label>Objekt</label><type>STRING</type></column>
<column no="4"><id>Datum</id><label>Datum</label><type>DATE</type></column>
</header>
<data>
<row>
<column no="1">...</column>
<column no="2">...</column>
<column no="3">...</column>
<column no="4">...</column>
</row>
<row>
<column no="1">...
</row>
</data>
</table>
<table name="Tabelle2" rows="42">
<header>
<column no="1"><id>KdName</id><label>KdName</label><type>STRING</type></column>
<column no="2"><id>KdNr</id><label>KdNr</label><type>STRING</type></column>
</header>
<data>
<row>
<column no="1">...</column>
<column no="2">...</column>
</row>
<row>
<column no="1">...
</row>
</data>
</table>
<table name="Tabelle3" rows="129">
<header>
<column no="1"><id>BName</id><label>BName</label><type>STRING</type></column>
<column no="2"><id>BNr</id><label>BNr</label><type>STRING</type></column>
<column no="3"><id>BObjekt</id><label>BObjekt</label><type>STRING</type></column>
<column no="4"><id>BAnz</id><label>BAnz</label><type>DATE</type></column>
<column no="5"><id>BJob</id><label>BJob</label><type>DATE</type></column>
<column no="6"><id>BState</id><label>BState</label><type>DATE</type></column>
</header>
<data>
<row>
<column no="1">...</column>
<column no="2">...</column>
<column no="3">...</column>
<column no="4">...</column>
<column no="5">...</column>
<column no="6">...</column>
</row>
<row>
<column no="1">...
</row>
</data>
</table>
<table name="Tabelle4" rows="209">
<header>
<column no="1"><id>DName</id><label>DName</label><type>STRING</type></column>
<column no="2"><id>DNr</id><label>DNr</label><type>STRING</type></column>
<column no="3"><id>DObjekt</id><label>DObjekt</label><type>STRING</type></column>
<column no="4"><id>DAnz</id><label>DAnz</label><type>DATE</type></column>
<column no="5"><id>DJob</id><label>DJob</label><type>DATE</type></column>
<column no="6"><id>DState</id><label>DState</label><type>DATE</type></column>
<column no="7"><id>DId</id><label>DId</label><type>DATE</type></column>
<column no="8"><id>DKat</id><label>DKat</label><type>DATE</type></column>
</header>
<data>
<row>
<column no="1">...</column>
<column no="2">...</column>
<column no="3">...</column>
<column no="4">...</column>
<column no="5">...</column>
<column no="6">...</column>
<column no="7">...</column>
<column no="8">...</column>
</row>
<row>
<column no="1">...
</row>
</data>
</table>
</report>
</xml>Mittlerweile kann ich jetzt jede Tabelle einzeln auswerten, die Zeit liegt jetzt unter 5 Sekunden pro Tabelle und damit im akzeptablen Rahmen.
Ziel ist also erreicht: Gesamte XML jetzt in vier Tabellen aufsplitten und die Daten in SQL nutzen
Dann machst du vermutlich auch 4 SQL Tabellen daraus und joinst dann die Daten? Klingt erstmal gut.
Genau XML-Datei enthält vier unterschiedliche Strukturen und die Daten kommen dann in vier Tabellen.
Was die Auswertung betrifft geht dass dann in den anderen Fachbereich.
Was die Auswertung betrifft geht dass dann in den anderen Fachbereich.
