Noeud XML de la valeur de mise à jour SQL Server

Il n'est pas possible de mettre à jour le XML dans plus d'un endroit à la fois, donc vous devez le faire dans une boucle quelconque. Le mieux que je pourrais find était d'extraire les ID du XML dans la Table2 et de se joindre à Table1.ID pour produire une table temporaire qui contient la position ordinale Table2.ID du noeud Data1 dans le XML ( OrdPos ) et la nouvelle valeur GUID .

Ensuite, vous pouvez faire une boucle sur le nombre maximal de nœuds présents dans la colonne XML et effectuer la mise à jour.

 -- Variable used to loop over nodes declare @I int -- Temp table to hold the work that needs to be done. create table #T ( ID int, -- ID from table2 OrdPos int, -- Ordinal position of node Data1 in root GUID uniqueidentifier, -- New ID primary key (OrdPos, ID) ) -- Shred the XML in Table2, join to Table1 to get GUID insert into #T(ID, OrdPos, GUID) select T2.ID, row_number() over(partition by T2.ID order by DN) as OrdPos, T1.GUID from Table2 as T2 cross apply T2.XMLCol.nodes('Root[1]/Data1') as D(N) inner join Table1 as T1 on T1.ID = DNvalue('(ID/text())[1]', 'int') -- Get the max number of nodes in one row that needs to be updated set @I = ( select top(1) count(*) from #T group by ID order by 1 desc ) -- Do the updates in a loop, one level at a time while @I > 0 begin update T2 set XMLCol.modify('replace value of (/Root[1]/Data1[sql:variable("@I")]/ID/text())[1] with sql:column("T.GUID")') from Table2 as T2 inner join #T as T on T2.ID = T.ID where T.OrdPos = @I set @I = @I - 1 end drop table #T 

SQL Fiddle

J'en ai un à mettre à jour.

Fermer, mais pas de cigare. Mais c'est la fin de la journée.

 IF OBJECT_ID('tempdb..#XmlHolderTable') IS NOT NULL begin drop table #XmlHolderTable end IF OBJECT_ID('tempdb..#ScalarHolderTable') IS NOT NULL begin drop table #ScalarHolderTable end CREATE TABLE #ScalarHolderTable ( ScalarKey int not null , Name varchar(16) , Phone varchar(16) , UUID uniqueidentifier ) CREATE TABLE #XmlHolderTable ( XmlSurrogateIdentityKey int not null identity (1001, 1), TheXml xml ) INSERT INTO #ScalarHolderTable ( ScalarKey , Name , Phone , UUID ) select 1 , 'HK' , 999 , NEWID() union all select 2 , 'RK' , 888 , NEWID() union all select 3 , 'SK' , 777 , NEWID() union all select 4 , 'PK' , 66 , NEWID() -- Declare XML variable DECLARE @data XML; -- Element-centered XML SET @data = N' <Root> <Data1> <ID>1</ID> <Name>HK</Name> </Data1> <Data1> <ID>2</ID> <Name>RK</Name> </Data1> </Root> '; INSERT INTO #XmlHolderTable ( TheXml) values ( @data ) select TheXml.value('(//Data1/ID)[1]','int') , * from #XmlHolderTable SELECT Data.Col.value('(.)[1]','Int') AS Id FROM #XmlHolderTable xmlHolder CROSS APPLY TheXml.nodes('//Data1/ID') AS Data(Col) /* SELECT Data.Col.value('(Id)[1]','Int') AS Id FROM @Data.nodes('/Root/Data') AS Data(Col) */ declare @counter int select @counter = 0 /* WHILE ( exists ( select top 1 null From #XmlHolderTable xmlHolder CROSS APPLY TheXml.nodes('//Data1/ID') AS Data(Col) , #ScalarHolderTable scalarHolder Where ISNUMERIC ( Data.Col.value('(.)[1]','varchar(40)') ) > 0 ) ) BEGIN select @counter= @counter + 1 print '/@counter/' print @counter print '' */ UPDATE #XmlHolderTable SET TheXml.modify('replace value of (//Data1/ID/text())[1] with sql:column("scalarHolder.UUID")') --select Data.Col.value('(.)[1]','Int') as MyValue , scalarHolder.ScalarKey From #XmlHolderTable xmlHolder CROSS APPLY TheXml.nodes('//Data1/ID') AS Data(Col) , #ScalarHolderTable scalarHolder Where Data.Col.value('(.)[1]','Int') = scalarHolder.ScalarKey /* END */ select * from #ScalarHolderTable select TheXml from #XmlHolderTable IF OBJECT_ID('tempdb..#XmlHolderTable') IS NOT NULL begin drop table #XmlHolderTable end IF OBJECT_ID('tempdb..#ScalarHolderTable') IS NOT NULL begin drop table #ScalarHolderTable end 

Voulez-vous absolument modifier le XML actuel? parce que si vous pouvez simplement le générer à partir de vos données, ce sera beaucoup plus simple:

 update Table2 set XMLCol = ( select T1.GUID as ID, T1.Name as Name from T2.XMLCol.nodes('Root/Data1') as T(C) inner join Table1 as T1 on T1.ID = TCvalue('ID[1]', 'int') and T1.Name = TCvalue('Name[1]', 'varchar(10)') for xml path('Data1'), root('Root'), type ) from Table2 as T2 

voir sql fiddle exemple

mise à jour Ok, autant que je sache, chaque Data1 n'a qu'un seul ID. Alors vous pouvez faire ceci:

 declare @temp table(ID int, T1_ID int, XMLcol xml) -- split xml, each ID goes in own row insert into @temp select ID, TCvalue('ID[1]', 'int') as ID, TCquery('.') as XMLCol from Table2 as T2 outer apply T2.XMLCol.nodes('Root/Data1') as T(C) -- modify xml update @temp set XMLCol.modify(' replace value of (Data1/ID/text())[1] with sql:column("T1.GUID") ') from @temp as T inner join Table1 as T1 on T1.ID = T.T1_ID -- modify original table update Table2 set XMLCol = ( select (select T.XMLcol) from @temp as T where T.ID = T2.ID for xml path(''), root('Root'), type ) from Table2 as T2