8
XML insert-update in einer MS SQL DB
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
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
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: 286922
Url: https://administrator.de/contentid/286922
Ausgedruckt am: 25.11.2024 um 10:11 Uhr
8 Kommentare
Neuester Kommentar
Hallo teslajr,
damit sollte das gehen:
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
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
Vielen Dank Max, dein Code löst mein Update Problem
Danke & Gruss
Danke & Gruss
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
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
Hallo teslajr,
das bekommst Du mit isnull (x.nachname, '') z.B. für den Nachnamen.
Gruß, Mad Max
das bekommst Du mit isnull (x.nachname, '') z.B. für den Nachnamen.
Gruß, Mad Max
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?
Danke & Gruss
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
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:
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);
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?
Danke & Gruss
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
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.:
oder
Gruß, Mad Max
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
1
