teslajr
Oct 28, 2015, updated at Oct 30, 2015 (UTC)
view1573
view8
0
Goto Top

XML insert-update in einer MS SQL DB

GermansolvedQuestionDatabasesDevelopment
Hallo Leute

Mein Code zum importieren einer xml in die MS SQL 2008 DB funktioniert, jedoch möchte ich dass die Daten bei bestehenden auch ein update machen und diese überschreiben.

wie müsste ein update aussehen?
(ich habe vieles ausprobiert, aber komme nicht weiter)

use testDB
declare @xmldata xml

set quoted_identifier on

set @xmldata=(select CONVERT(xml, bulkcolumn) as bulkcolumn
from openrowset(bulk 'd:\export.xml', single_blob)as x)

insert into dbo.testTableA
select Tab.Col.query('.').value('(/Mitarbeiter/Mitarbeiter-Nr.) [1]', 'varchar (50)') as mid,
Tab.Col.query('.').value('(/Mitarbeiter/Nachname) [1]', 'varchar (50)') as nachname,
Tab.Col.query('.').value('(/Mitarbeiter/Vorname) [1]', 'varchar (50)') as vorname,
Tab.Col.query('.').value('(/Mitarbeiter/Manager) [1]', 'varchar (50)') as manager,
Tab.Col.query('.').value('(/Mitarbeiter/SamAccountName) [1]', 'varchar (50)') as SamAccountName
from @xmldata.nodes('/Personal/Mitarbeiter') Tab (Col)
left join dbo.testTableA as z
on Tab.Col.query('.').value('(/Mitarbeiter/Mitarbeiter-Nr.) [1]', 'varchar (50)') = z.mid
and Tab.Col.query('.').value('(/Mitarbeiter/Nachname) [1]', 'varchar (50)') = z.name
and Tab.Col.query('.').value('(/Mitarbeiter/Vorname) [1]', 'varchar (50)') = z.vorname
and Tab.Col.query('.').value('(/Mitarbeiter/Manager) [1]', 'varchar (50)') = z.manager
and Tab.Col.query('.').value('(/Mitarbeiter/SamAccountName) [1]', 'varchar (50)') = z.SamAccountName
where z.mid is null

Danke & Gruss
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 286922

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

Printed on: April 24, 2024 at 16:04 o'clock

8 Comments
Latest comment
Goto Top
Member: MadMax
Solution MadMax Oct 28, 2015, updated at Oct 30, 2015 at 07:19:08 (UTC)
Goto Top
Hallo teslajr,

damit sollte das gehen:
merge	testTableA t
using	(
	select Tab.Col.query('.').value('(/Mitarbeiter/Mitarbeiter-Nr.) [1]', 'varchar (50)') as mid,  
	Tab.Col.query('.').value('(/Mitarbeiter/Nachname) [1]', 'varchar (50)') as nachname,  
	Tab.Col.query('.').value('(/Mitarbeiter/Vorname) [1]', 'varchar (50)') as vorname,  
	Tab.Col.query('.').value('(/Mitarbeiter/Manager) [1]', 'varchar (50)') as manager,  
	Tab.Col.query('.').value('(/Mitarbeiter/SamAccountName) [1]', 'varchar (50)') as SamAccountName  
	from xmldata.nodes('/Personal/Mitarbeiter') Tab (Col)  
	) x on x.mid = t.mid
when matched then update
	set	nachname = x.nachname,
		vorname = x.vorname,
		manager = x.manager,
		SamAccountName = x.SamAccountName
when not matched by target then insert
	(mid, nachname, vorname, manager, SamAccountName)
	values (x.mid, x.nachname, x.vorname, x.manager, x.SamAccountName);

Achja, nochwas: bevor ich dieses sperrige XML mehrmals verwende, würde ich das lieber in eine temporäre Tabelle schieben und dann mit der weitermachen.

Gruß, Mad Max
Member: teslajr
teslajr Oct 30, 2015 at 07:20:10 (UTC)
Goto Top
Vielen Dank Max, dein Code löst mein Update Problem

Danke & Gruss
Member: teslajr
teslajr Oct 30, 2015 at 14:21:35 (UTC)
Goto Top
Etwas hätte ich noch...ich bekomme eine logische Fehlermeldung:
Der Wert NULL kann in die 'TaetigkeitID'-Spalte, 'testDB.dbo.testTableA'-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei UPDATE.

wenn ich nur nachname und vorname update, wie kann ich es umgehen, dass felder welche einen wert verlangen nicht meckern?
(ohne die tabelle zu ändern, dass das feld auch ohne einen wert erstellt wird)

Danke & Gruss
Member: MadMax
MadMax Oct 30, 2015 at 18:05:10 (UTC)
Goto Top
Hallo teslajr,

das bekommst Du mit isnull (x.nachname, '') z.B. für den Nachnamen.

Gruß, Mad Max
Member: teslajr
teslajr Nov 02, 2015 at 15:36:44 (UTC)
Goto Top
Hallo Max

bin wieder am kämpfen, wo frage/setze ich dieses ISNULL?
habe schon alles versucht, ohne Erfolg.

Frage ich das beim einlesen der XML ab oder am schluss beim insert?

use testDB
declare @xmldata xml

set quoted_identifier on

set @xmldata=(select CONVERT(xml, bulkcolumn) as bulkcolumn
from openrowset(bulk '\\Server\export$\export.xml', single_blob)as x)  

merge	Person t
using	(
	select Tab.Col.query('.').value('(/Mitarbeiter/Personalnummer) [1]', 'varchar (50)') as PersonalNummer,  
	Tab.Col.query('.').value('(/Mitarbeiter/Nachname) [1]', 'varchar (50)') as nachname,  
	Tab.Col.query('.').value('(/Mitarbeiter/Vorname) [1]', 'varchar (50)') as vorname,  
	Tab.Col.query('.').value('(/Mitarbeiter/Initialen) [1]', 'varchar (50)') as kurzzeichen  
	from @xmldata.nodes('/Personal/Mitarbeiter') Tab (Col)  
	) x on x.PersonalNummer = t.PersonalNummer
when matched then update
	set	name = x.nachname,
		vorname = x.vorname,
		kurzzeichen = x.kurzzeichen
when not matched by target then insert
	(PersonalNummer, name, vorname, kurzzeichen)
	values ( x.PersonalNummer, x.nachname, x.vorname, x.kurzzeichen);

Danke & Gruss
Member: MadMax
MadMax Nov 02, 2015 at 19:52:14 (UTC)
Goto Top
Moin teslajr,

in Deinem Fall geht es an beiden Stellen. isnull prüft, ob der erste Wert NULL ist und gibt in dem Fall den zweiten Wert zurück. Ist auch alles in der Hilfe beschrieben, da sollte man als erstes mal reinschauen bei Problemen.

Für Deinen Befehl habe ich es mal beim insert/update für den Nachnamen angegeben:
use testDB
declare @xmldata xml

set quoted_identifier on

set @xmldata=(select CONVERT(xml, bulkcolumn) as bulkcolumn
from openrowset(bulk '\\Server\export$\export.xml', single_blob)as x)  

merge	Person t
using	(
	select Tab.Col.query('.').value('(/Mitarbeiter/Personalnummer) [1]', 'varchar (50)') as PersonalNummer,  
	Tab.Col.query('.').value('(/Mitarbeiter/Nachname) [1]', 'varchar (50)') as nachname,  
	Tab.Col.query('.').value('(/Mitarbeiter/Vorname) [1]', 'varchar (50)') as vorname,  
	Tab.Col.query('.').value('(/Mitarbeiter/Initialen) [1]', 'varchar (50)') as kurzzeichen  
	from @xmldata.nodes('/Personal/Mitarbeiter') Tab (Col)  
	) x on x.PersonalNummer = t.PersonalNummer
when matched then update
	set	name = isnull (x.nachname, ''),  
		vorname = x.vorname,
		kurzzeichen = x.kurzzeichen
when not matched by target then insert
	(PersonalNummer, name, vorname, kurzzeichen)
	values ( x.PersonalNummer, isnull (x.nachname, ''), x.vorname, x.kurzzeichen);
Member: teslajr
teslajr Nov 03, 2015 updated at 08:21:26 (UTC)
Goto Top
Guten Morgen Max

Vielen Dank für deine Hilfe, die Funktion isnull() habe ich schon verstanden, aber im Zusammenhang mit dem XML Import nicht.
Denn ich bekomme den Error dass die TaetigkeitID leer ist: Der Wert NULL kann in die 'TaetigkeitID'-Spalte, 'testDB.dbo.testTableA'-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu.

In der XML gibt es diese Spalte nicht, nur in der Tabelle, deswegen weiss ich nicht wo ich isnull(TaetigkeitID,'') machen soll?

when matched then update
	set	name = x.nachname,
		vorname = x.vorname,
		taetigkeitID = isnull(x.taetigkeitID, ''),  
		kurzzeichen = x.kurzzeichen		
when not matched by target then insert
	(taetigkeitID, PersonalNummer, name, vorname, kurzzeichen)
	values (isnull(x.taetigkeitID,''), x.PersonalNummer,  x.nachname, x.vorname, x.kurzzeichen);



Danke & Gruss
Member: MadMax
MadMax Nov 03, 2015 at 11:19:50 (UTC)
Goto Top
Hallo teslajr,

ok, jetzt versteh ich Dein Problem.

Du kannst da auch Variable oder Konstante setzen und mußt Dich nicht nur auf die Daten, die bei "using" stehen, beziehen. Da im Import scheinbar keine TaetigkeitID enthalten ist, brauchst Du das isnull dann auch nicht.

Ob Du beim Update die TaetigkeitID zurücksetzen mußt, weiß ich nicht, ansonsten kannst Du sie auch weglassen.

Beim Insert kannst Du statt Deines "isnull(x.taetigkeitID,'')" dann einfach eine 0 oder 1 oder sonstwas setzen. Oder eine Variable, in der irgendein ermittelter Standardwert dafür steht.

Also z.B.:
when matched then update
	set	name = x.nachname,
		vorname = x.vorname,
		kurzzeichen = x.kurzzeichen		
when not matched by target then insert
	(taetigkeitID, PersonalNummer, name, vorname, kurzzeichen)
	values (0, x.PersonalNummer,  x.nachname, x.vorname, x.kurzzeichen);

oder
when matched then update
	set	name = x.nachname,
		vorname = x.vorname,
		taetigkeitID = @taetigkeitID,
		kurzzeichen = x.kurzzeichen		
when not matched by target then insert
	(taetigkeitID, PersonalNummer, name, vorname, kurzzeichen)
	values (@taetigkeitID, x.PersonalNummer,  x.nachname, x.vorname, x.kurzzeichen);

Gruß, Mad Max
heart1
GermansolvedQuestionDatabasesDevelopment
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 CommentsjstrickerWireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 CommentsAlexWishaHow to set up and configure a Linux GRE tunnelAlexWisha - 3 Comments