onkel-ossi
Aug 23, 2021
view5299
view16
0
Goto Top

Import von XML-File in MS SQL-Server

GermansolvedQuestionXMLDevelopment
Hallo,

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 face-sad
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
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 1186739414

Url: https://administrator.de/contentid/1186739414

Printed on: April 25, 2024 at 06:04 o'clock

16 Comments
Latest comment
Goto Top
Member: GrueneSosseMitSpeck
GrueneSosseMitSpeck Aug 23, 2021 at 13:42:47 (UTC)
Goto Top
nimm den Datenimport/Export Assistenten, der unterstützt auch XML.
Und ich meine irgendwo konnte man am Ende auch ein SQL Statement abspeichern.
Member: wiesi200
wiesi200 Aug 23, 2021 at 18:16:20 (UTC)
Goto Top
Hallo, interessant wäre mal die Fehlermeldung
Member: ukulele-7
ukulele-7 Aug 24, 2021 at 06:21:10 (UTC)
Goto Top
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 face-sad
Ja ist ein Standard, einfach ist Geschmackssache. Was genau geht denn nicht? Liefert der Select sinnvolle Daten wenn im SSMS ausgeführt? Welche Fehlermeldung?
Member: onkel-ossi
onkel-ossi Aug 24, 2021 at 09:27:05 (UTC)
Goto Top
Es kommt keine Fehlermeldung, sondern dass o Datensätze betrofen sind:

(0 row(s) affected)

Aktuell versuche ich es im SQL Server Management Studio (SSMS)
Member: wiesi200
wiesi200 Aug 24, 2021 at 13:41:00 (UTC)
Goto Top
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 ...
Member: ukulele-7
ukulele-7 Aug 24, 2021 at 13:59:49 (UTC)
Goto Top
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?
Member: onkel-ossi
onkel-ossi Aug 25, 2021 at 06:50:01 (UTC)
Goto Top
Hallo ukulele-7,

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.
Member: wiesi200
wiesi200 Aug 25, 2021 at 06:55:43 (UTC)
Goto Top
Aber wenn ich das Insert weg lasse und nur ein Select habe liefert er keine Daten bzw. Leere Spalten
Member: onkel-ossi
onkel-ossi Aug 25, 2021 updated at 09:58:01 (UTC)
Goto Top
Hallo wiesi200,

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>
Member: ukulele-7
ukulele-7 Aug 25, 2021 at 07:15:10 (UTC)
Goto Top
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.
Member: wiesi200
wiesi200 Aug 25, 2021 at 07:18:45 (UTC)
Goto Top
Dein "Problem" bei deiner XML ist das die Spaltenbezeichnung etwas ungünstig ist.
Member: onkel-ossi
Solution onkel-ossi Aug 25, 2021 at 13:15:42 (UTC)
Goto Top
So, nach vielen Versuchen habe ich es jetzt hinbekommen!


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
Member: ukulele-7
ukulele-7 Aug 25, 2021 at 13:32:53 (UTC)
Goto Top
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.
Member: onkel-ossi
onkel-ossi Aug 25, 2021 updated at 16:25:56 (UTC)
Goto Top
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.

<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
Member: ukulele-7
ukulele-7 Aug 26, 2021 at 07:10:21 (UTC)
Goto Top
Dann machst du vermutlich auch 4 SQL Tabellen daraus und joinst dann die Daten? Klingt erstmal gut.
Member: onkel-ossi
onkel-ossi Aug 26, 2021 at 09:44:29 (UTC)
Goto Top
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.
GermansolvedQuestionXMLDevelopment
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 CommentsjstrickerWireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 Comments