Multi Record über XML auf gespeicherte Prozedur

Ich möchte das folgende XML als String in SQL übergeben und UPDATE die EventID abhängig von den vorhandenen Tabellenspalten, die von EventID und GROUP identifiziert wurden …..

 <Item> <GroupItem> <Group1>A</Group1> <Price>100</Price> <Location>Here</Location> </GroupItem> <GroupItem> <Group2>B</Group2> <Price>200</Price> <Location>There</Location> </GroupItem> <GroupItem> <Group3>C</Group3> <Price>300</Price> <Location>Everywhere</Location> </GroupItem> <EVENTID>12345</EVENTID> <MATCHED>100</MATCHED> </Item> 

Also in diesem Beispiel möchte ich die Tabellenzeile aktualisieren, die eine EventID von 12345 hat und Werte für Spalten GROUP1 = A : GROUP1 = A , GROUP2 = B , GROUP3 = C ….

Jede mögliche Hilfe, um das Chaos zu enträtseln, das ich bin (noch einmal) würde geschätzt ….

TARGET TABLE Struktur folgt:

 CREATE TABLE [dbo].[GROUPTABLE]( [EventID] [int] NOT NULL, [Group1A] [nvarchar](50) NULL, [Group1B] [nvarchar](50) NULL, [Group1C] [nvarchar](50) NULL, [Group1D] [nvarchar](50) NULL, [Group1_Location] [nvarchar](50) NULL, [Group1_Matched] [float] NULL, [Group2A] [nvarchar](50) NULL, [Group2B] [nvarchar](50) NULL, [Group2_Matched] [float] NULL, [Group2_Location] [nvarchar](50) NULL, [Group3A] [nvarchar](50) NULL, [Group3B] [nvarchar](50) NULL, [Group3_Matched] [float] NULL, [Group3_Location] [nvarchar](50) NULL, [Group4A] [nvarchar](50) NULL, [Group4B] [nvarchar](50) NULL, [Group4_Matched] [float] NULL, [Group4_Location] [nvarchar](50) NULL, CONSTRAINT [PK_GROUPTABLE] PRIMARY KEY CLUSTERED ( [EventID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 

Während dies nicht begrifflich sinnvoll ist – das ist nicht der genaue Tisch, den ich in meiner Anwendung verwende – (ich möchte nicht zu viel weg geben), aber diese Struktur imitiert genau das, was ich versuche, in meinem 'echten' Tisch zu tun. …

UPDATE2

Kopiere einfach den ganzen Codeblock in ein leeres Abfragefenster und probier es aus …

 /* This is your mimic table Btw: It is - in almost all cases! - a very bad idea to store related data in a table like data1, data2, data3... If possible you should introduce a table GroupValues with a foreign key onto group table */ CREATE TABLE [dbo].[GROUPTABLE]( [EventID] [int] NOT NULL, [Group1A] [nvarchar](50) NULL, [Group1B] [nvarchar](50) NULL, [Group1_Matched] [float] NULL, [Group1_Location] [nvarchar](50) NULL, [Group2A] [nvarchar](50) NULL, [Group2B] [nvarchar](50) NULL, [Group2_Matched] [float] NULL, [Group2_Location] [nvarchar](50) NULL, [Group3A] [nvarchar](50) NULL, [Group3B] [nvarchar](50) NULL, [Group3_Matched] [float] NULL, [Group3_Location] [nvarchar](50) NULL, [Group4A] [nvarchar](50) NULL, [Group4B] [nvarchar](50) NULL, [Group4_Matched] [float] NULL, [Group4_Location] [nvarchar](50) NULL, CONSTRAINT [PK_GROUPTABLE] PRIMARY KEY CLUSTERED ( [EventID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]; --here I insert two dummy rows INSERT INTO GROUPTABLE VALUES (1, 'Gr1A','Gr1B',111,'Gr1Location' ,'Gr2A','Gr2B',222,'Gr2Location' ,'Gr3A','Gr3B',333,'Gr3Location' ,'Gr4A','Gr4B',444,'Gr4Location' ) ,(12345,'Gr1A','Gr1B',111,'Gr1Location' ,'Gr2A','Gr2B',222,'Gr2Location' ,'Gr3A','Gr3B',333,'Gr3Location' ,'Gr4A','Gr4B',444,'Gr4Location' ); --one test to show the data SELECT * FROM dbo.GROUPTABLE; --Here's your XML DECLARE @XML XML= '<Item> <GroupItem> <Group1>A</Group1> <Price>100</Price> <Location>Here</Location> </GroupItem> <GroupItem> <Group2>B</Group2> <Price>200</Price> <Location>There</Location> </GroupItem> <GroupItem> <Group3>C</Group3> <Price>300</Price> <Location>Everywhere</Location> </GroupItem> <EVENTID>12345</EVENTID> <MATCHED>100</MATCHED> </Item>'; /* And this is the UPDATE If necessary you might use .query() to get the nodes and save repeated XPath-navigation Anyway: Much better was a <Item> <GroupItem group="A"> <Group>A</Group> <Price>100</Price> ... <GroupItem group="B"> <Group>A</Group> <Price>100</Price> ... to avoid differint element names */ UPDATE dbo.GROUPTABLE SET Group1A = @XML.value('(/Item/GroupItem/*[local-name(.)="Group1"])[1]','varchar(max)') ,Group2A = @XML.value('(/Item/GroupItem/*[local-name(.)="Group2"])[1]','varchar(max)') ,Group3A = @XML.value('(/Item/GroupItem/*[local-name(.)="Group3"])[1]','varchar(max)') ,Group4A = @XML.value('(/Item/GroupItem/*[local-name(.)="Group4"])[1]','varchar(max)') ,Group1B = @XML.value('(/Item/GroupItem/*[local-name(.)="Group1"]/../Price)[1]','varchar(max)') ,Group2B = @XML.value('(/Item/GroupItem/*[local-name(.)="Group2"]/../Price)[1]','varchar(max)') ,Group3B = @XML.value('(/Item/GroupItem/*[local-name(.)="Group3"]/../Price)[1]','varchar(max)') ,Group4B = @XML.value('(/Item/GroupItem/*[local-name(.)="Group4"]/../Price)[1]','varchar(max)') ,Group1_Matched = @XML.value('(/Item/MATCHED)[1]','float') ,Group2_Matched = @XML.value('(/Item/MATCHED)[1]','float') ,Group3_Matched = @XML.value('(/Item/MATCHED)[1]','float') ,Group4_Matched = @XML.value('(/Item/MATCHED)[1]','float') ,Group1_Location = @XML.value('(/Item/GroupItem/*[local-name(.)="Group1"]/../Location)[1]','varchar(max)') ,Group2_Location = @XML.value('(/Item/GroupItem/*[local-name(.)="Group2"]/../Location)[1]','varchar(max)') ,Group3_Location = @XML.value('(/Item/GroupItem/*[local-name(.)="Group3"]/../Location)[1]','varchar(max)') ,Group4_Location = @XML.value('(/Item/GroupItem/*[local-name(.)="Group4"]/../Location)[1]','varchar(max)') WHERE EventID=@XML.value('(/Item/EVENTID)[1]','int'); --Here you see, that the item' value changed for EventID=12345 SELECT * FROM dbo.GROUPTABLE; --Clean Up --DROP TABLE dbo.GROUPTABLE 

AKTUALISIEREN

Mit dieser Abfrage get alle Ihre data auf einmal

 SELECT @XML.value('(/Item/EVENTID)[1]','int') AS EventId ,@XML.value('(/Item/MATCHED)[1]','int') AS Matched ,ABvalue('local-name(*[position()=1][1])','varchar(max)') AS GroupName ,ABvalue('*[position()=1][1]','varchar(max)') AS FirstNode ,ABvalue('Price[1]','decimal(12,4)') AS Price ,ABvalue('Location[1]','varchar(max)') AS Location FROM @XML.nodes('/Item/GroupItem') AS A(B) 

Das Ergebnis

 12345 100 Group1 A 100.0000 Here 12345 100 Group2 B 200.0000 There 12345 100 Group3 C 300.0000 Everywhere 

bisherige

Eigentlich bezweifle ich, dass die Beschreibung, die du wirklich gibst, was du willst :-), aber das war ein Ansatz, um zu erreichen, was ich verstand:

 DECLARE @XML XML= '<Item> <GroupItem> <Group1>A</Group1> <Price>100</Price> <Location>Here</Location> </GroupItem> <GroupItem> <Group2>B</Group2> <Price>200</Price> <Location>There</Location> </GroupItem> <GroupItem> <Group3>C</Group3> <Price>300</Price> <Location>Everywhere</Location> </GroupItem> <EVENTID>12345</EVENTID> <MATCHED>100</MATCHED> </Item>'; 

Deklariere eine Tabellenvariable, um sie mit Testdaten zu füllen

 DECLARE @tbl TABLE(ID INT,Group1 VARCHAR(10),Group2 VARCHAR(10),Group3 VARCHAR(10)); INSERT INTO @tbl VALUES (3456,'Test1','Test2','Test3') ,(12345,'Test1','Test2','Test3'); SELECT * FROM @tbl; 

Das Ergebnis

 3456 Test1 Test2 Test3 12345 Test1 Test2 Test3 

Ändern Sie die Zeile mit ID = 12345

 UPDATE @tbl SET Group1 = @XML.value('(/Item/GroupItem/*[local-name(.)="Group1"])[1]','varchar(max)') ,Group2 = @XML.value('(/Item/GroupItem/*[local-name(.)="Group2"])[1]','varchar(max)') ,Group3 = @XML.value('(/Item/GroupItem/*[local-name(.)="Group3"])[1]','varchar(max)') WHERE ID=@XML.value('(/Item/EVENTID)[1]','int'); 

Das Ergebnis

 SELECT * FROM @tbl; 3456 Test1 Test2 Test3 12345 ABC